UserForm Export to PDF and Send E-mail

[isuryd]

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!!

 

[asjmoron]

I have built something specifically to do this... I could tweak it for you if you have the sample?

 

[isuryd]

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.

 

[isuryd]

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, _
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