Excel

Attaching Macro to Emailed Workbook

Ease of Use

Intermediate

Version tested with

2000 

Submitted by:

geekgirlau

Description:

Allows you to copy a sheet to a separate workbook and email it, keeping its worksheet code attached. 

Discussion:

I had a workbook in which an individual sheet was to be sent to various users. The sheet has a drawing object with a macro attached. The worksheet is copied to a new workbook and saved on a network drive, then sent as an attachment in Outlook. The macro resides on the sheet that is being copied rather than within a module, so the code is copied along with the sheet. The macro works fine if you open the saved workbook. However when you open the attachment in Outlook, it redirects the attached macro to the temporary location used for all Outlook attachments, even if you have hard-coded the full path for the macro. The solution is when you create the mail message, attach a shortcut to your workbook rather than embedding the workbook in the message. 

Code:

instructions for use 

			
Sub SendSheet() 
     
    Dim objOL As Outlook.Application ' MS Outlook application
    Dim objMail As Outlook.MailItem ' MS Outlook mail message
    Dim strAttachPath As String ' full path of filename
    Dim strTo As String ' Name of person to receive email
     
     
    On Error GoTo ErrHandler 
     
     ' set path to save file
     ' EDIT this path
    strAttachPath = "C:\Temp\Purchases_" & Format(Date, "yyyymmdd") & ".xls" 
     
     ' EDIT the name
    strTo = "Anne Kilmartin" 
     
     ' copy the sheet to a new workbook and save
    shSend.Copy 
     
     ' Need to save the workbook prior to attaching the macro
    Application.DisplayAlerts = False 
    ActiveWorkbook.SaveAs Filename:=strAttachPath 
    Application.DisplayAlerts = True 
     
     ' make sure the macro is attached to the button
     'EDIT the name of the macro
    Worksheets(1).Shapes(1).OnAction = "'" & strAttachPath & "'!shSend.MyMacro" 
     
     ' save and close the workbook
    ActiveWorkbook.Close True 
     
     ' create Outlook mail message
    On Error Resume Next 
    Set objOL = GetObject("", "Outlook.Application") 
     
    If Err.Number <> 0 Then 
        Set objOL = CreateObject("Outlook.Application") 
    End If 
     
    On Error GoTo ErrHandler 
    Set objMail = objOL.CreateItem(olMailItem) 
     
    With objMail 
        .To = strTo 
        .Subject = "Subject of my Email" 
        .Attachments.Add strAttachPath, olByReference 
         
         ' EDIT - change to .Send
        .Display 
    End With 
     
     
ExitHere: 
    On Error Resume Next 
    Set objMail = Nothing 
    Set objOL = Nothing 
    Exit Sub 
     
ErrHandler: 
    Select Case Err.Number 
         ' can't always connect to Outlook
    Case 429 
        MsgBox "Please try again later, or attach the file to an email manually" & vbCrLf & _ 
        strAttachPath, vbInformation, "Cannot connect to Outlook" 
         
    Case Else 
        MsgBox Err.Number & ": " & Err.Description 
    End Select 
     
    Resume ExitHere 
End Sub

How to use:

  1. Copy the code above.
  2. Create the workbook file you want to send.
  3. Create a sheet with a drawing object.
  4. Open the Visual Basic Editor (VBE) window by hitting Alt+F11.
  5. From the menu, choose Insert-Module.
  6. Paste the code into the window that appears at right.
  7. At left, in the Project Explorer, double-click on the sheet you want to e-mail.
  8. In the Properties Window, change the name to "shSend".
  9. Create a new macro in shSend (this is the macro you want to run when the drawing object is clicked - in my sample, the macro just displays a message).
  10. Edit the macro code appropriately.
  11. Go to Toosl-References, tick Microsoft Outlook x.0 Object Library and select OK (the number will depend on which version of MS Office you are running).
  12. Close the VBE.
 

Test the code:

  1. Hit Tools-Macro-Macros and double-click shSend.
 

Sample File:

Sendtest.zip 11.28KB 

Approved by mdmackillop


This entry has been viewed 250 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2020 VBA Express