|
|
|
|
|
|
|
|
|
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:
|
- Copy the code above.
- Create the workbook file you want to send.
- Create a sheet with a drawing object.
- Open the Visual Basic Editor (VBE) window by hitting Alt+F11.
- From the menu, choose Insert-Module.
- Paste the code into the window that appears at right.
- At left, in the Project Explorer, double-click on the sheet you want to e-mail.
- In the Properties Window, change the name to "shSend".
- 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).
- Edit the macro code appropriately.
- 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).
- Close the VBE.
|
|
Test the code:
|
- Hit Tools-Macro-Macros and double-click shSend.
|
|
Sample File:
|
Sendtest.zip 11.28KB
|
|
Approved by mdmackillop
|
|
This entry has been viewed 188 times.
|
|
|