UserForm Export to PDF and Send E-mail

isuryd

New Member
Joined
Aug 25, 2016
Messages
4
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!!
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
I have built something specifically to do this... I could tweak it for you if you have the sample?
 
Upvote 0
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: https://we.tl/t-usOJRKcf8R

Please let me know if this works.
 
Upvote 0
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:

Code:
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, _
OpenAfterPublish:=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


ExitHandler:
Exit Sub
ErrHandler:
    MsgBox "Could not create PDF File"
    Resume ExitHandler
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

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