I've written Excel VBA code that sends an email via Outlook.
Everywhere I've looked, example code for attaching files to an email is for static named files, as in, you're sending the same file name, with the same path every time.
The button that runs this macro is inside the workbook that I'm trying to attach.
Sub mySub
Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Outlook.recipient
Dim objOutlookAttach As Outlook.Attachment
Dim WeekendingDate As Date
With Worksheets("Macro Buttons")
WeekendingDate = Range("N2").Value
End With
Set objOutlook = CreateObject("Outlook.Application")
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
With objOutlookMsg
Set objOutlookRecip = .Recipients.Add("blah@blah")
objOutlookRecip.Type = olTo
.Subject = "Blah " & WeekendingDate
.Body = "blah blah blah"
'Add attachments to the message
[some code]
For Each objOutlookRecip In .Recipients
objOutlookRecip.Resolve
Next
If DisplayMsg Then
.Display
Else
.Save
End If
End With
Set objOutlook = Nothing
End Sub
The process is simple. You just need to copy the file's path (location) to the clipboard, then paste it in the File name field of the File Explorer (Finder) window in the email attachment window.
You need the Attachments.Add
code inserted into the MailItem setup:
With objOutlookMsg
Set objOutlookRecip = .Recipients.Add("blah@blah")
objOutlookRecip.Type = olTo
.Subject = "Blah " & WeekendingDate
.Body = "blah blah blah"
'Add attachments to the message [some code]
.Attachments.Add "pathToFile"
For Each objOutlookRecip In .Recipients
objOutlookRecip.Resolve
Next
If DisplayMsg Then
.Display
Else
.Save
End If
End With
Set objOutlook = Nothing
In one of my own scripts I pass a collection of attachments to the MailItem to be attached using a Dictionary object and the following code:
With oMailItem
Set .SendUsingAccount = oOutlook.Session.Accounts.Item(iAccount)
.To = EmailData("To")
.CC = EmailData("CC")
.BCC = EmailData("BCC")
.Subject = EmailData("Subject")
.Body = EmailData("Body")
sAttachArray = Split(EmailData("AttachmentPaths"), ";")
For Each sAttachment In sAttachArray
.Attachments.Add(sAttachment)
Next
.Recipients.ResolveAll
.Display ' debug mode - uncomment this to see email before it's sent out
End With
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With