+ Reply to Thread
Results 1 to 2 of 2

VBA - Using loops to create emails

  1. #1
    Registered User
    Join Date
    01-26-2015
    Location
    Melbourne,Australia
    MS-Off Ver
    2010
    Posts
    2

    Red face VBA - Using loops to create emails

    Hey all,
    I'm a VBA newbie who is trying to use a list from an excel worksheet to send email reminders to those that have there reservation expiry date in column K highlighted in red. my code works for the 1st email address but not the rest. Also in the body of the email i have the drawing number (Column B) and date (Column K) associated to the email address. I wanted my code to loop through column K and send email reminders to the email address in column L that have there date in Column K highlighted in red. In the body of the email i would like it to tell the recipients the drawing number and the reservation expiry date. I have attached a screenshot of the worksheet layout. I appreciate any help I can get.
    Thanks Guys
    ps. I'm using excel 2010 if that helps
    screenshot.jpg

  2. #2
    Registered User
    Join Date
    01-26-2015
    Location
    Melbourne,Australia
    MS-Off Ver
    2010
    Posts
    2

    Re: VBA - Using loops to create emails

    Here Is the code guys:
    Sub SendReminderMail()
    Dim OutLookApp As Outlook.Application
    Dim OutLookMailItem As Outlook.MailItem
    Dim C As Integer
    Dim C2 As Integer
    Dim MailDest As String
    Dim DrawNum As String

    ''Allow access to OutLook
    Set OutLookApp = CreateObject("OutLook.Application")
    Set OutLookMailItem = OutLookApp.CreateItem(0)

    On Error GoTo cleanup

    DrawNum = "": MailDest = ""
    For C2 = 1 To WorksheetFunction.CountA(Columns(11)) 'counter increments as it passes through the rows of Column 11 or K = reservation expiry

    If Cells(C2, 11).Interior.ColorIndex = 3 Then 'the drawing numbers with there expiry dates highligthed in red will be collected

    If DrawNum = "" Then

    DrawNum = Cells(C2, 11).Offset(0, -9).Value
    ElseIf DrawNum <> "" And Cells(C2, 11).Interior.ColorIndex = 3 Then

    DrawNum = "Your reservation for Electrical Drawing Number: " & Cells(C2, 11).Offset(0, -9).Value & " expires on the " & Cells(C2, 11).Value & "."
    End If

    MailDest = Cells(C2, 11).Offset(0, 1).Value

    With OutLookMailItem

    .To = MailDest
    .Subject = "Electrical Drawing Reminder"
    .Body = DrawNum
    .Display
    End With
    End If
    Next C2

    On Error Resume Next
    Set OutLookApp = Nothing
    Set OutLookMailItem = Nothing
    On Error GoTo 0

    ''once email is sent OutLookMailItem and OutLookApp are intialised
    ''error blocker, blocks irrelevant or neigible error messages from appearing
    cleanup:
    Set OutLookApp = Nothing
    Set OutLookMailItem = Nothing
    Application.ScreenUpdating = True
    End Sub

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Create multiple pivot tables/files with loops
    By ATXnative83 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-22-2014, 07:46 PM
  2. Loops through rows to create new sheet
    By missf in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-31-2011, 11:05 AM
  3. Create Emails from Table
    By shane.albrandt in forum Access Programming / VBA / Macros
    Replies: 1
    Last Post: 06-17-2010, 06:34 AM
  4. create and send emails using excel
    By twofootgiant in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-15-2010, 09:33 AM
  5. Do not create separate emails - concatenate in one
    By kamle_b in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-12-2007, 12:24 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1