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

asjmoron

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

isuryd

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

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

Forum statistics

Threads
1,082,141
Messages
5,363,368
Members
400,731
Latest member
Jackserver

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...
Top