Convert a workbook (included all worksheets) to pdf and attached to an email

Convert a workbook (included all worksheets) to pdf and attached to an email

What This Macro Does:

Prompts for Email Address: Uses an InputBox to ask you for the recipient’s email address.

Cancels if No Email is Entered: If you don’t enter an email, it will show a message and exit the macro.

Converts Workbook to PDF: Saves the entire workbook as a PDF file in the same location as the workbook.

Prepares the Email: Creates a new email in Outlook with the PDF attached and uses the entered email address.

Displays the Email: Opens the email for review before sending. Change .Display to .Send if you want the email to be sent automatically.

This macro will now ask for the recipient’s email every time you run it.

Macro VBA credit: Trifon Papadopoulos

Macro VBA

Sub ConvertWorkbookToPDFAndEmail()

Dim ws As Worksheet

Dim pdfFileName As String

Dim filePath As String

Dim OutApp As Object

Dim OutMail As Object

Dim recipientEmail As String

‘ Prompt user for recipient email

recipientEmail = InputBox(“Please enter the recipient’s email address:”, “Recipient Email”)

‘ Exit if no email is entered

If recipientEmail = “” Then

MsgBox “No recipient email entered. Operation canceled.”, vbExclamation

Exit Sub

End If

‘ Define the file path and name for the PDF

filePath = ThisWorkbook.Path & “\”

pdfFileName = filePath & ThisWorkbook.Name & “.pdf”

‘ Set all sheets to landscape, fit to one page wide, and add page numbers

For Each ws In ThisWorkbook.Worksheets

ws.PageSetup.Orientation = xlLandscape

ws.PageSetup.Zoom = False

ws.PageSetup.FitToPagesWide = 1

ws.PageSetup.FitToPagesTall = False ‘ This makes it adjust to the length of the content

‘ Add page numbers to the center of the footer

ws.PageSetup.CenterFooter = “Page &P of &N” ‘ &P = current page, &N = total pages

Next ws

‘ Export the entire workbook to a single PDF

ThisWorkbook.ExportAsFixedFormat Type:=xlTypePDF, Filename:=pdfFileName, _

Quality:=xlQualityStandard, IncludeDocProperties:=True, _

IgnorePrintAreas:=False, OpenAfterPublish:=False

‘ Create Outlook application object

Set OutApp = CreateObject(“Outlook.Application”)

Set OutMail = OutApp.CreateItem(0)

‘ Prepare email

With OutMail

.To = recipientEmail ‘ Use the email entered by the user

.CC = “”

.BCC = “”

.Subject = “Attached PDF of Workbook ” & “‘” & ThisWorkbook.Name & “‘”

.Body = “Please find attached the PDF version of the workbook ” & “‘” & ThisWorkbook.Name & “‘”

.Attachments.Add pdfFileName

‘ Display or send the email

.Display ‘ Change to .Send to send directly

End With

‘ Clean up

Set OutMail = Nothing

Set OutApp = Nothing

‘ Confirmation message

MsgBox “Workbook has been converted to a horizontal PDF with page numbering and attached to an email.”, vbInformation

End Sub