1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: automatic mail delivery based on date/time

Discussion in 'Business Applications' started by night-fury, Nov 27, 2014.

Thread Status:
Not open for further replies.
Advertisement
  1. night-fury

    night-fury Thread Starter

    Joined:
    Nov 27, 2014
    Messages:
    89
    Hi,

    I am in dire need of a solution wherein I want outlook to shoot an automatic email based on date from calendar or excel. Basically I have some users assigned some privileges which are assigned for a specific period. I need the mail to go out to them may be a day or two prior to the target date.

    I came across this thread:
    http://forums.techguy.org/business-applications/1129238-automatic-outlook-mail-due-date.html

    I copied the code the in outlook VBA editor (i already have one script there and since being new to this i do not know if two scripts would be ok to have in there....:( ...ScreenShot attached). However just to try when i ran the rule it gave me an error (screenshot attached).

    I can't reply to the same thread since that is closed.

    can you please help in this regards...

    -regards,
    gsg
     

    Attached Files:

  2. XCubed

    XCubed

    Joined:
    Feb 21, 2013
    Messages:
    520
    hi
    this needs to be in the Excel VBA module in the excel file that contains your email addresses, names an other information that needs to be included in your email.
     
  3. night-fury

    night-fury Thread Starter

    Joined:
    Nov 27, 2014
    Messages:
    89
    Awesome, it worked !!!!!!!!!!!!!! thank you so much for replying.

    --------------------------
    i finally tried after changing the text as per my need, guess I was mistaken about a part. This script will use the date to mention in the subject. Is there a way for the script to look in the date column and send mail to only those recipients who have, say today's date in the date column?
     
  4. XCubed

    XCubed

    Joined:
    Feb 21, 2013
    Messages:
    520
    The relevant code is highlighted


    Code:
    Sub eMail()
    Dim lRow As Integer
    Dim i As Integer
    Dim toDate As Date
    Dim toList As String
    Dim eSubject As String
    Dim eBody As String
    With Application
        .ScreenUpdating = False
        .EnableEvents = False
        .DisplayAlerts = False
    End With
    Sheets(1).Select
    lRow = Cells(Rows.Count, 4).End(xlUp).Row
    For i = 2 To lRow
    toDate = Replace(Cells(i, 3), ".", "/")
      If Left(Cells(i, 5), 4) <> "Mail" And [B][COLOR=red]toDate = Date[/COLOR][/B] Then
         Set OutApp = CreateObject("Outlook.Application")
         Set OutMail = OutApp.CreateItem(0)
            toList = Cells(i, 4)    'gets the recipient from col D
            eSubject = "Project " & Cells(i, 2) & " is due on " & Cells(i, 3)
            eBody = "Dear " & Cells(i, 1) & vbCrLf & vbCrLf & "Please update your project status."
            
            On Error Resume Next
            With OutMail
            .To = toList
            .CC = ""
            .BCC = ""
            .Subject = eSubject
            .Body = eBody
            .bodyformat = 1
            .Display   ' ********* Creates draft emails. Comment this out when you are ready
            '.Send     '********** UN-comment this when you  are ready to go live
            End With
     
        On Error GoTo 0
        Set OutMail = Nothing
        Set OutApp = Nothing
     Cells(i, 5) = "Mail Sent " & Date + Time 'Marks the row as "email sent in Column A"
    End If
    Next i
    ActiveWorkbook.Save
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
        .DisplayAlerts = True
    End With
    End Sub
     
  5. night-fury

    night-fury Thread Starter

    Joined:
    Nov 27, 2014
    Messages:
    89
    Superb !!!!!! thats sheer genius !! thanks a lot xcubed !
     
  6. flamebuster

    flamebuster

    Joined:
    Dec 2, 2014
    Messages:
    1
    awesome...i have been looking for something like this !!!!!

    is there a way to set a word to bold ? e.g. in the salutation, it will pick up a name from excel and write:

    Dear XYZ,

    .......... etc etc.

    Can we have this script to bold the words picked up from the name column (first column here) ?
     
  7. night-fury

    night-fury Thread Starter

    Joined:
    Nov 27, 2014
    Messages:
    89
    i am working on it. turns out I can use it too. i will post here once done.
     
  8. night-fury

    night-fury Thread Starter

    Joined:
    Nov 27, 2014
    Messages:
    89
    Hi,

    Here u go.

    Code:
    Sub eMail()
    Dim lRow As Integer
    Dim i As Integer
    Dim toDate As Date
    Dim toList As String
    Dim eSubject As String
    Dim eBody As String
    With Application
        .ScreenUpdating = False
        .EnableEvents = False
        .DisplayAlerts = False
    End With
    Sheets(1).Select
    lRow = Cells(Rows.Count, 4).End(xlUp).Row
    For i = 2 To lRow
    toDate = Replace(Cells(i, 3), ".", "/")
      If Left(Cells(i, 5), 4) <> "Mail" And toDate = Date Then
         Set OutApp = CreateObject("Outlook.Application")
         Set OutMail = OutApp.CreateItem(0)
            toList = Cells(i, 4)    'gets the recipient from col D
            ccList = Cells(i, 6)    'gets the Cc list from col E
            eSubject = "Access ID " & Cells(i, 2)
            eBody = "<html><head></head><body>Dear " & Cells(i, 1) & ",<br><br>" & "This is a test to check access type[COLOR="Red"][B] <b>[/B][/COLOR]" & Cells(i, 7) & "[COLOR="red"][B]</b>[/B][/COLOR] against access ID.</body></html> "
            On Error Resume Next
            With OutMail
            .To = toList
            .CC = ccList
            .BCC = ""
            .Subject = eSubject
            .HTMLBody = eBody
            '.bodyformat = olFormatHTML ' ****** OR .bodyformat = 2olFormatHTML (1 is for plain text, 2 is for HTML/rich text)
            .Display   ' ********* Creates draft emails. Comment this out when you are ready
           [COLOR="Red"][B] '.Send [/B] [/COLOR]   '********** UN-comment this when you  are ready to go live
            End With
     
        On Error GoTo 0
        Set OutMail = Nothing
        Set OutApp = Nothing
     Cells(i, 5) = "Mail Sent " & Date + Time 'Marks the row as "email sent in Column A"
    End If
    Next i
    ActiveWorkbook.Save
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
        .DisplayAlerts = True
    End With
    End Sub
    
    
    the 'ebody' type is changed to html and then html tags- are used to bold feed from one of the columns. I have also attached the test file. It i set only to display the mail and not send it. You can enable the sending by un-commenting .send (marked red in above code).

    hope this helps.

    All credit goes to Xcubed.
     

    Attached Files:

  9. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1138132

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice