Adding Date to Body of email, based on unique text string in it.

I am new to running vba in OutLook 2016, but was wondering if there is a way to run a vba macro on the body of an email prior to sending?

I have multiple areas within the body of a template email that have date fields in them and I would like to add Today+1 in those fields.

Examples:
TargetDeliveryDate##yyyy-mm-dd##
ActualDeliveryDate##yyyy-mm-dd##

I would like the macro to find the text string in the body of the email and insert the date for us.  In the first example it would be Today+1 or current date+1 and in the second one Today+2

Is this possible and if so, how would I go about this?  Please advise and thanks.
RWayneHAsked:
Who is Participating?
 
Rgonzo1971Connect With a Mentor Commented:
then try
Sub ChangeDates()
    Dim NewMail As Outlook.MailItem
    Set myMessage = Application.ActiveInspector.CurrentItem
    'Determine Body type
    
     If myMessage.BodyFormat = olFormatHTML Then
    
       myMessage.HTMLBody = Replace(myMessage.HTMLBody, "TargetDeliveryDate##yyyy-mm-dd##", "TargetDeliveryDate##" & Format(Date + 1, "yyyy-mm-dd") & "##")
       myMessage.HTMLBody = Replace(myMessage.HTMLBody, "ActualDeliveryDate##yyyy-mm-dd##", "ActualDeliveryDate##" & Format(Date + 2, "yyyy-mm-dd") & "##")
    
    Else
        
        myMessage.Body = Replace(myMessage.Body, "TargetDeliveryDate##yyyy-mm-dd##", "TargetDeliveryDate##" & Format(Date + 1, "yyyy-mm-dd") & "##")
        myMessage.Body = Replace(myMessage.Body, "ActualDeliveryDate##yyyy-mm-dd##", "ActualDeliveryDate##" & Format(Date + 2, "yyyy-mm-dd") & "##")
    End If

End Sub

Open in new window

0
 
Rgonzo1971Commented:
Hi,

pls try
myMessage.HTMLBody = Replace(myMessage.HTMLBody, "TargetDeliveryDate##yyyy-mm-dd##", "TargetDeliveryDate##" & Format(Date +1, "yyyy-mm-dd") & "##")
myMessage.HTMLBody = Replace(myMessage.HTMLBody, "ActualDeliveryDate##yyyy-mm-dd##", "ActualDeliveryDate##" & Format( Date +2, "yyyy-mm-dd") & "##")

Open in new window

Maybe you have to replace # with HTML #
if not HTML then simply
myMessage.Body = Replace(myMessage.Body, "TargetDeliveryDate##yyyy-mm-dd##", "TargetDeliveryDate##" & Format(Date +1, "yyyy-mm-dd") & "##")
myMessage.Body = Replace(myMessage.Body, "ActualDeliveryDate##yyyy-mm-dd##", "ActualDeliveryDate##" & Format( Date +2, "yyyy-mm-dd") & "##")

Open in new window


Regards
1
 
RWayneHAuthor Commented:
Is there a way to gather what Body type it is and use which ever one that needs?  When I tried to run this

When I tried to run this I got a Run-time error '424' Object required.  Was I supposed to dim something?

Something like:
Sub ChangeDates()
    
    'Determine Body type
    
    'If BodyType = HTMLBody
    
       myMessage.HTMLBody = Replace(myMessage.HTMLBody, "TargetDeliveryDate##yyyy-mm-dd##", "TargetDeliveryDate##" & Format(Date + 1, "yyyy-mm-dd") & "##")
       myMessage.HTMLBody = Replace(myMessage.HTMLBody, "ActualDeliveryDate##yyyy-mm-dd##", "ActualDeliveryDate##" & Format(Date + 2, "yyyy-mm-dd") & "##")
    
    'ElseIf BodyType = Body
        
        myMessage.Body = Replace(myMessage.Body, "TargetDeliveryDate##yyyy-mm-dd##", "TargetDeliveryDate##" & Format(Date + 1, "yyyy-mm-dd") & "##")
        myMessage.Body = Replace(myMessage.Body, "ActualDeliveryDate##yyyy-mm-dd##", "ActualDeliveryDate##" & Format(Date + 2, "yyyy-mm-dd") & "##")
    'End If

End Sub

Open in new window

0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
Rgonzo1971Commented:
you have to get the mailitem first
Sub ChangeDates()
    Dim NewMail As Outlook.MailItem
    Set myMessage= Application.ActiveInspector.currentItem
    'Determine Body type
    
     If myMessage.BodyFormat = olFormatHTML  Then
    
       myMessage.HTMLBody = Replace(myMessage.HTMLBody, "TargetDeliveryDate##yyyy-mm-dd##", "TargetDeliveryDate##" & Format(Date + 1, "yyyy-mm-dd") & "##")
       myMessage.HTMLBody = Replace(myMessage.HTMLBody, "ActualDeliveryDate##yyyy-mm-dd##", "ActualDeliveryDate##" & Format(Date + 2, "yyyy-mm-dd") & "##")
    
    Else
        
        myMessage.Body = Replace(myMessage.Body, "TargetDeliveryDate##yyyy-mm-dd##", "TargetDeliveryDate##" & Format(Date + 1, "yyyy-mm-dd") & "##")
        myMessage.Body = Replace(myMessage.Body, "ActualDeliveryDate##yyyy-mm-dd##", "ActualDeliveryDate##" & Format(Date + 2, "yyyy-mm-dd") & "##")
    End If

End Sub

Open in new window

0
 
RWayneHAuthor Commented:
Thanks... it appears to run thru the macro just fine, but nothing chgs.  It is taking HTMLBody if.  Any ideas why this will not chg them?
0
 
RWayneHAuthor Commented:
Does this matter if the email has a name?  Mine generates a name of: RemedyForce Ticket: Process Automation/Testing - Message(HTML)

Wondering if that may have something to do with it not working?
0
 
Rgonzo1971Commented:
then try
Sub ChangeDates()
    Dim NewMail As Outlook.MailItem
    Set myMessage= Application.ActiveInspector.currentItem
    'Determine Body type
    
     If myMessage.BodyFormat = olFormatHTML  Then
    
       myMessage.HTMLBody = Replace(myMessage.HTMLBody, "TargetDeliveryDate##yyyy-mm-dd##", "TargetDeliveryDate##" & Format(Date + 1, "yyyy-mm-dd") & ##")
       myMessage.HTMLBody = Replace(myMessage.HTMLBody, "ActualDeliveryDate##yyyy-mm-dd##", "ActualDeliveryDate##" & Format(Date + 2, "yyyy-mm-dd") & ##")
    
    Else
        
        myMessage.Body = Replace(myMessage.Body, "TargetDeliveryDate##yyyy-mm-dd##", "TargetDeliveryDate##" & Format(Date + 1, "yyyy-mm-dd") & "##")
        myMessage.Body = Replace(myMessage.Body, "ActualDeliveryDate##yyyy-mm-dd##", "ActualDeliveryDate##" & Format(Date + 2, "yyyy-mm-dd") & "##")
    End If

End Sub

Open in new window

0
 
RWayneHAuthor Commented:
Compile error: Expected: expression  Ln8 and 9

I am putting this in a new module, is that ok? or does it need to go in ThisOutlookSession?
0
 
RWayneHAuthor Commented:
That worked!!!  Thanks so much..
0
 
RWayneHAuthor Commented:
Appreciate the help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.