Auto save to PDF and email macro

razor_raef

New Member
Joined
Sep 15, 2019
Messages
13
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 :)
 

Some videos you may like

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

razor_raef

New Member
Joined
Sep 15, 2019
Messages
13
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
 

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
3,078
.
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
 

razor_raef

New Member
Joined
Sep 15, 2019
Messages
13
.
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)
 

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
3,078
.
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.
 

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
3,078
.
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:

Watch MrExcel Video

Forum statistics

Threads
1,095,481
Messages
5,444,736
Members
405,298
Latest member
fxtrtr17

This Week's Hot Topics

Top