none
How to Create Macro to Insert Data into Body of E-mail RRS feed

  • Question

  • I have an Outlook Template that I used daily to send an e-mail with the date and daily bank balances.  Every day, I have to change the date and 4 numbers.  How can I write a macro in Outlook so that it prompts me to change the data?  I have been overtyping the data every day, but I always seem to forget to change something, then I have to send out another "oops" e-mail with the correction.  I have no idea how to write a macro in Outlook, can someone help me?  Here is what the body of the e-mail says:

    The Cash Book has been updated through Monday, October 13, 2014. 

    Beginning Balance:  $1,486,311

    Cash In:  $36,060

    Cash Out:  -$87,369

    Ending Balance:  $1,435,002

    Let me know if you have any questions.  Thanks!

    I want to write a macro to prompt me for the date and each of the four $ amounts.  Where do I start?  I wish there was a "Record Macro" option like there is in Excel.  Can anyone help?  Thanks in advance.


    Wednesday, October 15, 2014 6:49 PM

All replies

  • Where are you getting those values from? If they are in Excel, then there is no need for the prompting.
    Wednesday, October 15, 2014 7:40 PM
  • I am manually entering the values into the body of the e-mail, they are not in Excel or anything. 
    Wednesday, October 15, 2014 10:04 PM
  •     

    Try this in Outlook:

    Sub EMailAskForValuesOutlook()

        Dim OutMail As MailItem

        Set OutMail = Application.CreateItem(0)

        On Error Resume Next
        With OutMail
            .To = "Kelvinator@Kelvinator.org"
            .CC = ""
            .BCC = ""
            .Subject = "This is the Subject line"
            .Body = "The Cash Book has been updated through " & Format(DateValue(InputBox("What date?")), "dddd, mmmm d, yyyy") & Chr(10) & Chr(10)
            .Body = .Body & "Beginning Balance:  " & Format(CDbl(InputBox("Beginning Balance?")), "$#,###") & Chr(10) & Chr(10)
            .Body = .Body & "Cash In:  " & Format(CDbl(InputBox("Cash In?")), "$#,###") & Chr(10) & Chr(10)
            .Body = .Body & "Cash Out:  " & Format(CDbl(InputBox("Cash Out?")), "$#,###") & Chr(10) & Chr(10)
            .Body = .Body & "Ending Balance:  " & Format(CDbl(InputBox("Ending Balance?")), "$#,###") & Chr(10) & Chr(10)
            .Body = .Body & "Let me know if you have any questions.  Thanks!"
            .Display
            If MsgBox("Send it?", vbYesNo) = vbYes Then .Send
        End With

        Set OutMail = Nothing

    End Sub

    In Excel, the code would be this, requiring a reference to MS Outlook

        

    Sub EMailAskForValuesExcel()

        Dim OutApp As Outlook.Application
        Dim OutMail As Outlook.MailItem

        Set OutApp = CreateObject("Outlook.Application")
        Set OutMail = OutApp.CreateItem(0)

        On Error Resume Next
        With OutMail
            .To = "Kelvinator@Kelvinator.org"
            .CC = ""
            .BCC = ""
            .Subject = "This is the Subject line"
            .Body = "The Cash Book has been updated through " & Format(DateValue(InputBox("What date?")), "dddd, mmmm d, yyyy") & Chr(10) & Chr(10)
            .Body = .Body & "Beginning Balance:  " & Format(CDbl(InputBox("Beginning Balance?")), "$#,###") & Chr(10) & Chr(10)
            .Body = .Body & "Cash In:  " & Format(CDbl(InputBox("Cash In?")), "$#,###") & Chr(10) & Chr(10)
            .Body = .Body & "Cash Out:  " & Format(CDbl(InputBox("Cash Out?")), "$#,###") & Chr(10) & Chr(10)
            .Body = .Body & "Ending Balance:  " & Format(CDbl(InputBox("Ending Balance?")), "$#,###") & Chr(10) & Chr(10)
            .Body = .Body & "Let me know if you have any questions.  Thanks!"
            .Display
            If MsgBox("Send it?", vbYesNo) = vbYes Then .Send
        End With

        Set OutMail = Nothing
        Set OutApp = Nothing
    End Sub



    Thursday, October 16, 2014 12:49 PM
  • We can at least check if it is current date or not. Below is the code.

    It check every mail being sent for 

    • Check if email body has text "The Cash Book has been updated through".
    • If found above text then it checks for current date and warns if it is not current date.
    Private Sub Application_ItemSend(ByVal oItem As Object, Cancel As Boolean)
       If TypeOf oItem Is Outlook.MailItem Then
          Cancel = Not (CheckEmailDateforDailyBankBalances(oItem))
       End If
    End Sub
      
    Private Function CheckEmailDateforDailyBankBalances(oMail As Outlook.MailItem) As Boolean
     
     
        Dim bSend As Boolean
      
        bSend = True
        
        If InStr(oMail.Body, "The Cash Book has been updated through") <> 0 Then
            
          If InStr(oMail.Body, Format(Now(), "DDDD, MMMM DD, YYYY")) = 0 Then
            bSend = (MsgBox("Date value should be : " & Format(Now(), "DDDD, MMMM DD, YYYY") & vbCrLf & "Do you want to Cancel? ", vbYesNo) = vbNo)
          End If
        End If
        
      CheckEmailDateforDailyBankBalances = bSend
      
    End Function

    In Outlook, Press Alt+F11 and paste above code in 'ThisOutlookSession' 

     
    Thursday, October 16, 2014 1:12 PM
  • Please see this link, and post back with any/all questions.

    http://www.rondebruin.nl/win/s1/outlook/mail.htm


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Thursday, October 16, 2014 1:47 PM
  • Bernie, this almost worked.  I copied and pasted your macro into an Outlook macro and ran it.  Here is what I got:

    Beginning Balance:  $1,363,729

    Cash In:  $607,575

    Cash Out:  -$16,165

    Ending Balance:  $1,955,139

    Let me know if you have any questions.  Thanks!

    The part about the cash book being updated through such-and-such date did not appear, although it did prompt me for a date and I entered one.  Also, my usual automatic signature does not appear, how do I get it to automatically insert it without inserting it manually?  Thanks so much for your reply, we're almost there!  :)

    Thursday, October 16, 2014 5:39 PM
  • When prompted, did you enter the date as US-centric M/D/Y? Otherwise, it may error out, though I could not get it to NOT work.

    I don't know how to add the signature to the email, but as a work-around, try changing


            .Body = .Body & "Let me know if you have any questions.  Thanks!"

    To

         .Body = .Body & "Let me know if you have any questions.  Thanks!" & Chr(10) & Chr(10)

         .Body = "Kelvin Ator" & Chr(10) & "Money Guy" & Chr(10) & "Telephone:  123-234-3456"

    or you can find your signature file and follow the instructions here:

    http://www.rondebruin.nl/win/s1/outlook/signature.htm

    I no longer have outlook, so I cannot test this part.




    Thursday, October 16, 2014 5:58 PM
  • Bernie:

    No, I was spelling out the whole date, I didn't know that it had to be in a certain format.  It works when I just put the date in the format you specified, although the sentence doesn't have a period at the end of it.  Since I have no idea how you are making this work, I can't edit the macro to put the period at the end.  Is that hard to do?

    One more thing...I want to insert a sentence after the first sentence and then have the l*nk to the file, to that the whole sentence reads like this:

    The Cash Book has been updated through Monday, October 13, 2014.  Here is the l*nk to the file: _________

    Thanks, Bernie, you have been a HUGE help!!!

    Thursday, October 16, 2014 7:14 PM
  • For the period, change this line

    .Body = "The Cash Book has been updated through " & Format(DateValue(InputBox("What date?")), "dddd, mmmm d, yyyy") & Chr(10) & Chr(10)

    to

    .Body = "The Cash Book has been updated through " & Format(DateValue(InputBox("What date?")), "dddd, mmmm d, yyyy") & "." & Chr(10)

    To add a link, though, I think you need to change .Body (in all cases) to .HTMLBody and add a line like

    .HTMLBody = .HTMLBody & "Here is the link to the file: <a href='valid link path'>click here</a>" & & Chr(10) & Chr(10)


    Thursday, October 16, 2014 7:35 PM
  • Bernie:

    Where do I insert the link?  Does it go between the < > or do I need to put all that other text like "click here" in it?  Sorry, I have no idea about writing macros, so I have to be shown exactly what to write or I can't get it to work.

    I also wanted to put a salutation at the beginning, and I copied your text lines exactly and I can't get it to work.  If I insert it at the end, it works, but if I put it at the beginning, it doesn't show up.  Here is my macro:

    Sub EMailAskForValuesOutlook()

        Dim OutMail As MailItem

        Set OutMail = Application.CreateItem(0)

        On Error Resume Next
        With OutMail
            .To = "freeman@farmfreshtoyou.com;tbarsotti@farmfreshtoyou.com;noah@capayorganic.com;jeff@farmfreshtoyou.com;cmurphy@farmfreshtoyou.com;mfoos@farmfreshtoyou.com"
            .CC = ""
            .BCC = ""
            .Subject = "Updated Cash Book"
            .Body = "Hi All:" & Chr(10) & Chr(10)
            .Body = "The Cash Book has been updated through " & Format(DateValue(InputBox("What date?")), "dddd, mmmm d, yyyy") & "." & Chr(10) & Chr(10)
            .Body = .Body & "Beginning Balance:  " & Format(CDbl(InputBox("Beginning Balance?")), "$#,###") & Chr(10) & Chr(10)
            .Body = .Body & "Cash In:  " & Format(CDbl(InputBox("Cash In?")), "$#,###") & Chr(10) & Chr(10)
            .Body = .Body & "Cash Out:  " & Format(CDbl(InputBox("Cash Out?")), "$#,###") & Chr(10) & Chr(10)
            .Body = .Body & "Ending Balance:  " & Format(CDbl(InputBox("Ending Balance?")), "$#,###") & Chr(10) & Chr(10)
            .Body = .Body & "Let me know if you have any questions.  Thanks!"
            .Display
            If MsgBox("Send it?", vbYesNo) = vbYes Then .Send
        End With

        Set OutMail = Nothing

    End Sub

    You can see that I added "Hi All:" at the beginning, but when I run the macro, the e-mail body begins with the line, "The Cash Book has been updated..."  Just to see if I was doing it right, I moved it to the end and it showed up.  How can I get it at the beginning?  Thanks again, Bernie.

    Thursday, October 16, 2014 8:21 PM
  • You need to append using .Body = .Body &

           

            .Body = "Hi All:" & Chr(10) & Chr(10)
            .Body = .Body & "The Cash Book has been updated through " & Format(DateValue(InputBox("What date?")), "dddd, mmmm d, yyyy") & "." & Chr(10) & Chr(10)

    Where is the file - do you have a link to the file? Is it consistent? Do you just overwrite the existing file with the new file?

    Thursday, October 16, 2014 8:24 PM
  • Okay, the .Body = .Body & thing worked!  :)

    The link is always the same.  So, I would like it to say "Here is the link to the file:" on the same line as "The Cash Book has been updated..."  Then show the link (which is constant).  Thanks!

    Thursday, October 16, 2014 8:28 PM
  • For the link to work use this - replace  M:\Test.doc  with the actual file link.

    Sub EMailAskForValuesOutlook()

        Dim OutMail As MailItem

        Set OutMail = Application.CreateItem(0)

        On Error Resume Next
        With OutMail
            .To = "freeman@farmfreshtoyou.com;tbarsotti@farmfreshtoyou.com;noah@capayorganic.com;jeff@farmfreshtoyou.com;cmurphy@farmfreshtoyou.com;mfoos@farmfreshtoyou.com"
            .CC = ""
            .BCC = ""
            .Subject = "Updated Cash Book"
            .HTMLBody = "Hi All:<P>"
            .HTMLBody = .HTMLBody & "The Cash Book has been updated through " & Format(DateValue(InputBox("What date?")), "dddd, mmmm d, yyyy") & ".<P><P>"
            .HTMLBody = .HTMLBody & "Here is the link to the file: <a href='M:\Test.doc'>Click here for the file</a><P>"
            .HTMLBody = .HTMLBody & "Beginning Balance:  " & Format(CDbl(InputBox("Beginning Balance?")), "$#,###") & "<P>"
            .HTMLBody = .HTMLBody & "Cash In:  " & Format(CDbl(InputBox("Cash In?")), "$#,###") & "<P>"
            .HTMLBody = .HTMLBody & "Cash Out:  " & Format(CDbl(InputBox("Cash Out?")), "$#,###") & "<P>"
            .HTMLBody = .HTMLBody & "Ending Balance:  " & Format(CDbl(InputBox("Ending Balance?")), "$#,###") & "<P>"
            .HTMLBody = .HTMLBody & "Let me know if you have any questions.  Thanks!"
            .Display
            If MsgBox("Send it?", vbYesNo) = vbYes Then .Send
        End With

        Set OutMail = Nothing

    End Sub

    Thursday, October 16, 2014 9:23 PM
  • Bernie:

    Okay, we're really close!  I had added my signature at the bottom before we changed all the .Body to .HTML, and used  & Chr(10) to have each line single spaced after the other.  Now they are all on one line (my name, title, phone number, e-mail, etc.)  When I put a & "<P>" at the end of each line, then it double spaces the lines, and in my signature I want single spaces.  What is the command to enter a single space after each line?

    Thanks again, Bernie!

    Thursday, October 16, 2014 9:44 PM
  • Replace <P> with <BR>
    Thursday, October 16, 2014 10:26 PM
  • That worked great, thanks!  One more question and then I'll leave you alone.  :)  I took out the <P> at the end of the sentence that says "The Cash Book has been updated..." so that the sentence that says "Here is..." appears on the same line as the sentence above it.  That works great except the two sentences run together, like this:

    The Cash Book has been updated through Wednesday, October 15, 2014.Here is the

    How do I insert two spaces between "2014" and "Here is the...?"  I tried putting spaces after the period in the macro but that didn't work. 

    Thanks again, Bernie for all your help, you're a genius!!!

    Thursday, October 16, 2014 11:01 PM
  • Just put a space between the " and the Here:

    "Here....

    " Here....


    Friday, October 17, 2014 1:17 AM
  • The thing to remember about Outlook Message bodies is that they are essentially Word documents, so you can more or less format them using Word VBA, once you have access to the body range. Thus you could create your message as follows:


    Sub SendAMessage()
    'No reference to Word or Outlook required
    Dim oOutlookApp As Object
    Dim oItem As Object
    Dim olInsp As Object
    Dim wdDoc As Object
    Dim oRng As Object
    Dim strMessage As String
    Dim sStartBal As Single
    Dim sCashIn As Single
    Dim sCashOut As Single
    Dim sEndBalance As Single

        strMessage = "The Cash Book has been updated through "
        On Error Resume Next
        'Get Outlook if it's running
        Set oOutlookApp = GetObject(, "Outlook.Application")

        'Outlook wasn't running, start it from code
        If Err <> 0 Then
            Set oOutlookApp = CreateObject("Outlook.Application")
        End If

    'Get the variable data
        strMessage = strMessage & InputBox("Enter Date", "Date", Format(Date, "dddd, mmmm d, yyyy")) & vbCrLf
        sStartBal = InputBox("Enter beginning balance", "Beginning Balance")
        sCashIn = InputBox("Enter cash in", "Cash In")
        sCashOut = InputBox("Enter cash out", "Cash Out")
        sEndBalance = sStartBal + sCashIn - sCashOut

    'and add it into the body text string
        strMessage = strMessage & "Beginning Balance:  $" & CStr(sStartBal) & vbCrLf
        strMessage = strMessage & "Cash In:  $" & CStr(sCashIn) & vbCrLf
        strMessage = strMessage & "Cash Out:  -$" & CStr(sCashOut) & vbCrLf
        strMessage = strMessage & "Ending Balance:  $" & CStr(sEndBalance) & vbCrLf

        'Create a new mailitem
        Set oItem = oOutlookApp.CreateItem(0)
        With oItem
            .BodyFormat = 2 'html
            Set olInsp = .GetInspector
            Set wdDoc = olInsp.WordEditor
            Set oRng = wdDoc.Range 'The message body
            oRng.collapse 1 'Collapse the range to the start (thus retaining the signature)
            oRng.Text = strMessage 'Insert the message text into the range
            .Display 'This is essential
            .To = "someone@somewhere.com" 'Change to your recipient
            .Subject = "This is the subject" 'Change to the subject of the message
        End With

        'Clean up
        Set oItem = Nothing
        Set oOutlookApp = Nothing
        Set olInsp = Nothing
        Set wdDoc = Nothing
    lbl_Exit:
        Exit Sub
    End Sub


    Graham Mayor - Word MVP
    www.gmayor.com



    Friday, October 17, 2014 5:51 AM