Add shortcut instead of file to Email with Macro
 

I have the following code where a macro in an excel
spreadsheet saves the current file then attaches a copy
of it to a e-mail. I would like it to now add an
attachment to the file instead of a copy of the file, in
the body of the email.

Thank you in advance
Rich

Here is my code

Private Sub CommandButton1_Click()
Application.DisplayAlerts = False
ThisWorkbook.Save
Application.DisplayAlerts = True
Dim oOLapp As Object
Dim oMailItem As Object
Set oOLapp = CreateObject("Outlook.Application")
Set oMailItem = oOLapp.CreateItem(0)
With oMailItem

.Attachments.Add "\\Simba\CSHARE\Employee Forms\Sales
Log sheets\NHS\Co#4 NHS Daily sales log.xls"
.Subject = "Co#4 NHS Daily sales log for Review"
.Body = "Here are Yesterday's Sales"
.Recipients.Add "Joseph Calabrese"
.Recipients.Add "Larry Schreiber"
.Recipients.Add "Craig Schreiber"
.Recipients.Add "Joe Hurley"
.Recipients.Add "Rich Pieri"
.Display '(or .Send if you don't want to look at it
first
Set oOLapp = Nothing
Set oMailItem = Nothing
End With

Application.Visible = True
Application.EnableEvents = True
ThisWorkbook.Close
End Sub

Add shortcut instead of file to Email with Macro
 

See

http://www.rondebruin.nl/sendmail.htm#body



--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"Richard Pieri" wrote in message ...
I have the following code where a macro in an excel
spreadsheet saves the current file then attaches a copy
of it to a e-mail. I would like it to now add an
attachment to the file instead of a copy of the file, in
the body of the email.

Thank you in advance
Rich

Here is my code

Private Sub CommandButton1_Click()
Application.DisplayAlerts = False
ThisWorkbook.Save
Application.DisplayAlerts = True
Dim oOLapp As Object
Dim oMailItem As Object
Set oOLapp = CreateObject("Outlook.Application")
Set oMailItem = oOLapp.CreateItem(0)
With oMailItem

.Attachments.Add "\\Simba\CSHARE\Employee Forms\Sales
Log sheets\NHS\Co#4 NHS Daily sales log.xls"
.Subject = "Co#4 NHS Daily sales log for Review"
.Body = "Here are Yesterday's Sales"
.Recipients.Add "Joseph Calabrese"
.Recipients.Add "Larry Schreiber"
.Recipients.Add "Craig Schreiber"
.Recipients.Add "Joe Hurley"
.Recipients.Add "Rich Pieri"
.Display '(or .Send if you don't want to look at it
first
Set oOLapp = Nothing
Set oMailItem = Nothing
End With

Application.Visible = True
Application.EnableEvents = True
ThisWorkbook.Close
End Sub