How to create a macro and button to pre-populate an email?

Do you need to create a button in your word document when clicked opens a new email message with the following fields populated:

  1. “To” address
  2. Subject
  3. Text in the body of the email
  4. Attaches the “word doc” you had opened in pdf and word format.

You would think this is a standard request. I don’t know why Microsoft makes it so difficult for us non-programmers. Why not create a button where the codes are pre-populated and all you need to do is the insert the variables such as the email address and text. Anyway after a day of googling, I finally figured it out. To save time for you people out there, here is a step by step guide of how.

Step 1 – Create the macro

  1. Open up the relevant word doc you would like to insert the button.
  2. Go to the “developers” TAB and then click on “Visual Basics”.
  3. Right click mouse button to insert a “module”.
  4. Then cut and paste the following code into the module and change variables (eg. email recipient’s address).

——-

Sub Send_original_and_pdf()
' This macro creates a pdf-file of the current document and adds
' both the original and the pdf-version of the document as an
' attachment to a new Outlook message.
' This macro requires
' -Word 2007
' -The SaveAsPDFandXPS.exe addin to be installed
' -A reference added to the Microsoft Outlook <version> Object Library
' The SaveAsPDFandXPS.exe addin can be downloaded from;
' http://www.microsoft.com/downloads/details.aspx?FamilyId=4D951911-3E7E-4AE6-B059-A2E79ED8704
    On Error Resume Next
    'Verify if the docment has been saved before so that we have a path to work with.
    'If not, notify the user that there will be a safe dialog first.
    If ActiveDocument.Path <> "" Then
        ActiveDocument.Save
    Else
        Dim Msg, Style, Title, Response
        Msg = "This document has not been saved before." & vbLf & _
        "Please save the document to disk first." & vbLf & _
        "Without saving first, only the pdf-file will be attached."
        Style = vbInformation + vbOKOnly
        Title = "Save current presentation"
        Response = MsgBox(Msg, Style, Title)

        Dim dlgSaveAs As FileDialog
        Dim strCurrentFile As String
        Set dlgSaveAs = Application.FileDialog(msoFileDialogSaveAs)

        If dlgSaveAs.Show = -1 Then
            strCurrentFile = dlgSaveAs.SelectedItems(1)
            ActiveDocument.SaveAs (strCurrentFile)
        End If
        Set dlgSaveAs = Nothing
    End If

    'Get the name of the open file and strip any extension.
    Dim MyFile As String
    MyFile = ActiveDocument.Name
    intPos = InStrRev(MyFile, ".")
    If intPos > 0 Then
        MyFile = Left(MyFile, intPos - 1)
    End If

    'Get the user's TempFolder to store the created pdf item in.
    Dim FSO As Object, TmpFolder As Object
    Set FSO = CreateObject("scripting.filesystemobject")
    Set FileName = FSO.GetSpecialFolder(2)

    'Create the full path name for the pdf-file
    FileName = FileName & "\" & MyFile & ".pdf"

    'Save the current document as pdf in the user's temp folder.
    'Note that we are going to include the document properties as well.
    'If you do not want this set "IncludeDocProps" to False.
    ActiveDocument.ExportAsFixedFormat OutputFileName:= _
        FileName, ExportFormat:= _
        wdExportFormatPDF, OpenAfterExport:=False, OptimizeFor:= _
        wdExportOptimizeForPrint, Range:=wdExportAllDocument, From:=0, To:=0, _
        Item:=wdExportDocumentContent, IncludeDocProps:=True, KeepIRM:=True, _
        CreateBookmarks:=wdExportCreateNoBookmarks, DocStructureTags:=True, _
        BitmapMissingFonts:=True, UseISO19005_1:=False

    'Declare an Outlook application an a mail item.
    Dim oOutlookApp As Outlook.Application
    Dim oItem As Outlook.MailItem
    Dim objOutlookRecip As Outlook.Recipient

    'Start Outlook if it isn't running.
    Set oOutlookApp = GetObject(, "Outlook.Application")
    If Err <> 0 Then
        Set oOutlookApp = CreateObject("Outlook.Application")
    End If

    'Create a new message.
    Set oItem = oOutlookApp.CreateItem(olMailItem)

    With oItem

              ' Add the To recipient(s) to the message.
              Set objOutlookRecip = .Recipients.Add("Enteremail@here.com.au")
              objOutlookRecip.Type = olTo

             ' Set the Subject, Body, and Importance of the message.
             .Subject = "Enter subject tagline"
             .Body = "Enter text to body here"

            .Importance = olImportanceHigh  'High importance

             ' Resolve each Recipient's name.
             For Each objOutlookRecip In .Recipients
                 objOutlookRecip.Resolve
             Next

          End With
          Set objOutlook = Nothing

    'Add the attachments.
    oItem.Attachments.Add FileName
    oItem.Attachments.Add ActiveDocument.FullName

    'Show the message.
    oItem.Display

    'Cleanup
    Set FSO = Nothing
    Set FileName = Nothing
    Set oOutlookApp = Nothing
    Set oItem = Nothing

End Sub

Step 2 – Create the button to call the above macro

  1. Open up the relevant word doc you would like to insert the button.
  2. Go to the “developers” TAB and select the “button” icon in the x-active control toolbar.
  3. Insert button in the desired location.
  4. Open visual basics (ensure button is highlighted)
  5. Enter the blue code below. The other codes should already be pre-populated for a  button.
  6. Close VB screen and rename button as desired by right clicking mouse and selecting EDIT.
Private Sub CommandButton1_Click()
Call Send_original_and_pdf
End Sub

Step 3 – You are done. Exit “design mode” and you are ready to go!

Note: I am NO programmer. The above codes resulted from merging and modifying codes I found on the web. It’s a bit of trial and error. I’m not technical so there may be more efficient codes out there. All I know is that this served my purpose. I hope it may help you too.

Advertisements

6 thoughts on “How to create a macro and button to pre-populate an email?

    • Sure you can. I was creating a form and wanted a submit button at the end of my form to make it convenient for the user (eg. do not need to type email address etc). Also I wanted to ensure that the subject tagline was consistent and had certain details as it was going to a group inbox.

  1. Hey there! This is my 1st comment here so I just wanted to give a quick shout out and tell you I
    truly enjoy reading your posts. Can you recommend any other blogs/websites/forums that cover the same topics?
    Thanks a lot!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s