VBA to attachment file from cell path

Hello All,

I am trying to add attachment from path referenced in column I, but not working. Can anyone help me on this?

Thank you a million.

Sub ExcelToOutlookSR()
Dim mApp As Object
Dim mMail As Object
Dim SendToMail As String
Dim MailSubject As String
Dim mMailBody As String
For Each r In Selection
SendToMail = Range("B" & r.Row)
SendCCMail = Range("D" & r.Row)
MailSubject = Range("E" & r.Row)
mMailBody = Range("H" & r.Row)
mAttachment = Range("I" & r.Row)
Set mApp = CreateObject("Outlook.Application")
Set mMail = mApp.CreateItem(0)
With mMail
.To = SendToMail
.CC = SendCCMail
.Subject = MailSubject
.Body = mMailBody
.Attachments.Add = mAttachment
.Display
End With
Next r
End Sub

So Excel is telling me there is something wrong with the code, asking me to debug. Column I only has path, not file name. Can you please tell me what the format of the path should be, if it’s not just a path itself?

Thank you very much for your help.

Hi,

Thank you for your input. The path is formatted correctly, but I am still getting the error message.

Quote from Carim

Please feel free to comment ...

haha, no worries. So the path i have is copied from the "Copy Path" located on the left upper corner of the folder.

Thanks.

"P:\CDD Business Office\Medical Form - HIPPA Safe\2022\12, December 2022\Individual File\Espinoza.docx"

Quote from Carim

Sorry but I cannot see what is exactly in front of you eyes ...

In order to 100 % sure ... you can use File Explorer ... it will show you the full path as well as the exact file name and the extension ...

Thank you everyone, for your time on this.

I figured it out, turns out I need to remove the "=" on line 20

.Attachments.Add = mAttachment should be .Attachments.Add mAttachment (see Correction below)

Sub ExcelToOutlookSR()
Dim mApp As Object
Dim mMail As Object
Dim SendToMail As String
Dim MailSubject As String
Dim mMailBody As String
For Each r In Selection
SendToMail = Range("B" & r.Row)
SendCCMail = Range("D" & r.Row)
MailSubject = Range("E" & r.Row)
mMailBody = Range("H" & r.Row)
mAttachment = Range("I" & r.Row)
Set mApp = CreateObject("Outlook.Application")
Set mMail = mApp.CreateItem(0)
With mMail
.To = SendToMail
.CC = SendCCMail
.Subject = MailSubject
.Body = mMailBody
' Correction for the Specific instruction for the Attachment ''''''''''''''''''''  
.Attachments.Add mAttachment
.Display
End With
Next r
End Sub

Truly appreciate your time and help on this.

Quote from Carim

Pleased to hear you have managed to fix your problem

Actually, I would like to take this 1 step further. I would like to have a company logo inserted in the email body rather than the email attachment as a file.

.Attachments.Add "W:\Business\BUSINESS OFFICE\CDD Logo.jpg" is where the logo located in the shared drive.

What do I need to do to make this happen?

Thank you.

Sub ExcelToOutlookSR()
Dim mApp As Object
Dim mMail As Object
Dim SendToMail As String
Dim MailSubject As String
Dim mMailBody As String
For Each r In Selection
SendToMail = Range("B" & r.Row)
SendCCMail = Range("D" & r.Row)
MailSubject = Range("E" & r.Row)
mMailBody = Range("H" & r.Row)
mAttachment = Range("I" & r.Row)
Set mApp = CreateObject("Outlook.Application")
Set mMail = mApp.CreateItem(0)
With mMail
.To = SendToMail
.CC = SendCCMail
.Subject = MailSubject
.Body = mMailBody
.Attachments.Add mAttachment
.Attachments.Add "W:\Business\BUSINESS OFFICE\CDD Logo.jpg"
.Display
.Send
End With
Next r
End Sub

I have set the signature as default, but I guess it's not translating into the process.

Thanks.

Quote from rory

It's much easier if you can use an Outlook template message with the picture already embedded.