Email button with file path

[Mc2388]

Hi all,

I have tried using the below code to add an Active X button to automatically send an email. I have gotten this to work but wanted to add some static text to the email body along with the file path of the current workbook and was wondering if someone could help? If possible also I’d like to have the current file name quoted in the subject line?

Any help and suggestions greatly appreciated!

Private Sub CommandButton1_Click()
Dim xOutApp As Object
Dim xOutMail As Object
Dim xMailBody As String
On Error Resume Next
Set xOutApp = CreateObject("Outlook.Application")
Set xOutMail = xOutApp.CreateItem(0)
xMailBody = "Body content" & vbNewLine & vbNewLine & _
"This is line 1" & vbNewLine & _
"This is line 2"
On Error Resume Next
With xOutMail
.To = "Email Address"
.CC = ""
.BCC = ""
.Subject = "Test email send by button clicking"
.Body = xMailBody
.Display
End With
On Error GoTo 0
Set xOutMail = Nothing
Set xOutApp = Nothing
End Sub

 

[tcaslv]

This is my thread I started with a simmilar path of what you want but with out the button asking to send the email. It sends automatically. Which works in my case. it includes the code to send the info you want. Maybe you can combine the 2 if you really need the active x button.

https://www.mrexcel.com/forum/excel-questions/1050053-vba-code-send-email-notification.html

 

[Mc2388]

Hi tcaslv,

Thanks for the response, I had seen your post but had hoped to include the current workbooks filepath without having to manually amend it every time I move the workbook itself. I also wanted to display the email before sending, to allow for an ad hoc message.

Any suggestions again appreciated!

 

[tcaslv]

If you refer back to the code I pasted, there is a comment section on the bottom after the string. I made the mistake in the begining and didnt know those were comments my self. But anything with a " ' " before the text is your comments and usually let you know some options you have for a particular string.

.Send '.Send to skip preview .Display to preview email

If you see at the begining of the string, the ".send" with out the quotes is your option for the string. If you just want to view the email before it sends out, change ".send" to ".display" again with out the quotes. I know there is a code for placing the filepath where the workbook is currently at, but last time I messed with it, it didnt produce good results. Im still green to the code, but the above suggestion should at least help on your other issue.

 

[Mc2388]

Thanks tcaslv,
Any ideas about adding a filepath that will stay updated as file may be moved between folders? Also if possible adding the file name to subject line?

Cheers!