William has attended:
Excel VBA Intro Intermediate course
Excel Advanced course
Working with the Outlook application
I was wondering if you had any code to hand that would allow me to open and compose a new email, attach an excel spreadsheet and then send it to a designated recipient using Excel VBA?
Beyond this, could you point me in the direction of the Microsoft add-in that turns off the automatic warnings when composing and sending emails via code?
Many thanks,
Wil House
RE: Working with the Outlook application
Hi William
Thank you for your question
The required code is given below. There are two procedures, and the second one calls the first one
[code]Public Sub SendEmail(ByVal Recipient As String, ByVal Attachment As String)
Dim Outlook As Object
Dim NameSpace As Object
Dim MailItem As Object
Set Outlook = CreateObject("Outlook.Application")
Set MailItem = Outlook.CreateItem(0)
With MailItem
.Subject = "Employee Report"
.Recipients.Add Recipient
.Body = "Workbook attached"
.Attachments.Add Attachment
.Send
End With
End Sub
Sub MailCall()
strAddress = InputBox("Enter Email Address")
strFileName = "C:\VBA + Complete.xls" 'needs to change to reflect actual location
Application.DisplayAlerts = False
SendEmail strAddress, strFileName
Application.DisplayAlerts = True
End Sub/code]
Hope this is useful
Regards
Stephen
