Thread: [Solved] Save a .docm file as .pdf and email .pdf as attachment
View Single Post
 
Old 02-19-2019, 10:00 AM
Sydney Lacey Sydney Lacey is offline Windows 10 Office 2016
Novice
  
Join Date: Feb 2019
Posts: 2
Sydney Lacey is on a distinguished road
Default Save a .docm file as .pdf and email .pdf as attachment
Standard "newbieness" apology... I'm fairly new to VBA beyond macros and simple code to streamline my own workflow. I thought this little project for my bosses would be a simple next step for me. I've been messing around with it for a few days and actually have been able to problem solve most of the code already and learned quite a bit, however, I seem to be stuck.

I need to allow end users to fill out a .docm form and save and send with one button click. So far, my code handles saving the file as a .pdf (creating the filename from Content Controls in the form) and places the new .pdf document in the Document folder.

The problem is the code I have below attaches and sends the .docm file instead of the .pdf.

If possible, I'd prefer to have the .pdf saved to whatever path/folder location the original .docm folder is located in and obviously, I need the .pdf to be the email attachment. The eventual email address will be a Microsoft Team site and I've discovered that macro-enabled documents are not Team friendly.

If I need to let the save path for the .pdf remain in the user's Document folder, I can live with that as long as that .pdf can be attached to the email.

My current code for a Test File I've been meddling with is as follows:

Code:
Private Sub btnSubmit_Click()

strName = ActiveDocument.SelectContentControlsByTitle("ddName")(1).Range.Text
strDate = ActiveDocument.SelectContentControlsByTitle("ddDate")(1).Range.Text
strTest = ActiveDocument.SelectContentControlsByTitle("ddTestNumber")(1).Range.Text

Dim strFilename As String
strFilename = strName & "_" & "VBATestFile_" & strTest & "_" & Format(strDate, "yyyymmdd") & ".pdf"

ActiveDocument.SaveAs2 strFilename, FileFormat:=wdFormatPDF

Dim OL          As Object
Dim EmailItem   As Object
Dim Doc         As Document

Application.ScreenUpdating = False
Set OL = CreateObject("Outlook.Application")
Set EmailItem = OL.CreateItem(olMailItem)
Set Doc = ActiveDocument
Doc.Save

With EmailItem
    .Subject = strName & " Test " & strTest
    .Body = "Test email send for " & strName & " " & strTest & "."
    .To = "email address"
    .Importance = olImportanceNormal
    .Attachments.Add Doc.FullName
    .Send
End With

Application.ScreenUpdating = True

MsgBox "Form Submitted", vbInformation

Set Doc = Nothing
Set OL = Nothing
Set EmailItem = Nothing

End Sub
Reply With Quote