Auto save to PDF and email macro

razor_raef

New Member
Joined
Sep 15, 2019
Messages
19
Hi all,

I've confused myself once again and can't figure this one out.

I'm trying to figure out the macro to save file as a PDF in the Set Printing Area and attach the PDF file to new email template in outlook so that way the user can write who it's got to be emailed to. And if it's possible, for the subject line and body to have the following:

Subject: Coaching Feedback Form
Body: Hi *Cell P1*,

Please find attached your coaching feedback form for this month.

Yours sincerely,
*Cell P2*

Thanks all :)
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
This is something I found and created earlier but in this version, the email automatically sends and doesn't come up with the pop up box or attaches a PDF.

Private Sub CommandButton1_Click()
'Updated by Extendoffice 2017/9/14
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 = "Below is my test results." & vbNewLine & vbNewLine & _
"The required pass mark is 90%." & vbNewLine & _
"I received " & Range("F51").Value & "%." & vbNewLine & _
"My incorrect answers were" & Range("F55").Value & ""

On Error Resume Next
With xOutMail
.To = [f52].Value
.CC = ""
.BCC = ""
.Subject = "My Test Results"
.Body = xMailBody
.Send
End With
On Error GoTo 0
Set xOutMail = Nothing
Set xOutApp = Nothing
End Sub
 
Upvote 0
.
Presuming the email macro you posted functions as desired, the following edits will attach a pdf file
and display the email prior to sending.

Code:
Option Explicit


Private Sub CommandButton1_Click()
'Updated by Extendoffice 2017/9/14
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 = "Below is my test results." & vbNewLine & vbNewLine & _
"The required pass mark is 90%." & vbNewLine & _
"I received " & Range("F51").Value & "%." & vbNewLine & _
"My incorrect answers were" & Range("F55").Value & ""


On Error Resume Next
With xOutMail
.To = [f52].Value
.CC = ""
.BCC = ""
.Subject = "My Test Results"
.Body = xMailBody


.Attachments.Add "C:\Users\My\Desktop\MyPDFFile.pdf"   '<--- Change MY to name of your computer and name of pdf file
                                                        ' pdf file is being stored on your desktop.
                                                        ' you can change path to any location desired.
'.Send
.Display


End With
On Error GoTo 0
Set xOutMail = Nothing
Set xOutApp = Nothing
End Sub
 
Upvote 0
.
Presuming the email macro you posted functions as desired, the following edits will attach a pdf file
and display the email prior to sending.

Code:
Option Explicit


Private Sub CommandButton1_Click()
'Updated by Extendoffice 2017/9/14
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 = "Below is my test results." & vbNewLine & vbNewLine & _
"The required pass mark is 90%." & vbNewLine & _
"I received " & Range("F51").Value & "%." & vbNewLine & _
"My incorrect answers were" & Range("F55").Value & ""


On Error Resume Next
With xOutMail
.To = [f52].Value
.CC = ""
.BCC = ""
.Subject = "My Test Results"
.Body = xMailBody


.Attachments.Add "C:\Users\My\Desktop\MyPDFFile.pdf"   '<--- Change MY to name of your computer and name of pdf file
                                                        ' pdf file is being stored on your desktop.
                                                        ' you can change path to any location desired.
'.Send
.Display


End With
On Error GoTo 0
Set xOutMail = Nothing
Set xOutApp = Nothing
End Sub

Alrighty, so I was able to come up with the following coding which now populates the pop up email but I was unable to get it to add an attachment of the current workbook:
Sub Testemailsend()
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 = "Test Email Body." & vbNewLine & _
"Test Email Body." & vbNewLine & _
"Test Email Body." & Range("F51").Value & "" & vbNewLine & _
"Test Email Body." & Range("F55").Value & ""




On Error Resume Next
With xOutMail
.To = [f52].Value
.CC = ""
.BCC = ""
.Subject = "Test Email Subject"
.Body = xMailBody




.Attachments.Add "\\mau.group\corporate\Homefolders$\rlakmas\Desktop"


'.Send
.Display




End With
On Error GoTo 0
Set xOutMail = Nothing
Set xOutApp = Nothing
End Sub



I guess another way for me to explain what it is that i'm trying to make it look like is once the user presses the macro button, it replicates the following process:
File > Save & Send > Send Using E-mail > Send As Attachment

When that pop up appears, the Subject and Body just have some pre-filled information similar to my coding above.

I feel like I may be over complicating the process but I can't imagine it's this hard to code it.

(Example of coding I found that doesn't work out of the box and needs Debugging and doesn't work quite right: https://www.thespreadsheetguru.com/blog/vba-guide-sending-excel-attachments-through-outlook)
 
Upvote 0
.
Is the workbook to be attached located in a network or is it located on a personal
desktop computer ?

The reason I ask is the path you provided to the desktop doesn't have a drive letter indicated.
I am not familiar with network drives and cannot advise you on how that type path would
look.
 
Upvote 0
.
Also, your path does not specifically name which file on the desktop is to be attached.

Don't 'assume' Excel knows which one you want. You must include the file name with extension in the path.

Lastly, the supplied macro presumes you have already saved the file as PDF to that location. This macro does not
auto convert the workbook to PDF first.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,496
Messages
6,113,995
Members
448,539
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top