UserForm Export to PDF and Send E-mail


New Member
Aug 25, 2016
Hello! I have created a UserForm in Excel for conversations that our Supervisors will be having with front-line employees. The goal of this workbook is for the Supervisor to have conversations with 5 employees on a daily basis to enhance employee engagement.

With that being said, I have created a UserForm with text and combo boxes with the information that needs to be recorded. There are 3 buttons at the bottom of the form.

1. Save Form - This button saves all the information that the Supervisor typed into a hidden sheet ("Data") so that we can keep a log of these conversations.
2. E-mail Form - This is where I would like for the Supervisor to click in order to send an e-mail to the team leaders, with an attachment of the form. *my current problem area
3. Close Form - This button saves the workbook and closes the UserForm, but keeps the workbook open (preference).

I created a sheet in the workbook titled "PAR Form Data" that that mimics the UserForm and pastes all the information into the correct areas. I need to be able to save this sheet as a PDF (without prompt) and to attach the PDF to an e-mail when the user clicks the "E-mail Form" button in the UserForm.

On the right hand side of the "PAR Form Data", I have a distribution list. The e-mail distribution list depends on the department, as well as, the supervisor filling out the form. Therefore, the distribution list will be changing constantly. The "To", "CC:", "Subject", and "PDF file name" of the e-mail all need to reference cells within the "PAR Form Data" sheet.

We will have this form saved in a network drive for our supervisors to utilize.

I have a sample file that I can upload.

Help is greatly appreciated!!


Board Regular
Apr 26, 2016
I have built something specifically to do this... I could tweak it for you if you have the sample?


New Member
Aug 25, 2016
Thank you, asjmoron. I do have a sample file, but for some reason I do not have the capabilities to attach any files due to my posting permissions. I've uploaded the file through WeTransfer. The link for the file is:

Please let me know if this works.


New Member
Aug 25, 2016
With A LOT of effort, I was able to figure this out. I am going to include the code in case someone else needs help with the same thing:

Dim myFile As VariantDim strFile As String

'enter name and select folder for file
' start in current workbook folder
strFile = ws.Range("N9") & ".pdf"
strFile = ThisWorkbook.Path & "\" & strFile

ws.ExportAsFixedFormat Type:=xlTypePDF, Filename:=strFile, _
Quality:=xlQualityStandard, IncludeDocProperties:=False, IgnorePrintAreas:=False, _

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 = "Please see attached PAR Conversation."

On Error Resume Next

With xOutMail
        .To = ws.Range("N1")
        .CC = ws.Range("N6")
        .Subject = ws.Range("N8")
        .Body = xMailBody
        .Attachments.Add ThisWorkbook.Path & "\" & ws.Range("N9") & ".pdf"
        .Display   'or use .Send
    End With
    On Error GoTo 0
    Set xOutMail = Nothing
    Set xOutApp = Nothing

Exit Sub
    MsgBox "Could not create PDF File"
    Resume ExitHandler

Forum statistics

Latest member

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...