asked on

Save a .docm file as a .pdf and email .pdf as attachment via Outlook (or send directly to SharePoint folder)

I have a .docm form that I need to code to do the following on a button click:

1. Assign a FileName based data from the Content Controls.
--- This is working currently. ---
2. Save the file with the created FileName as a .pdf (preferably in the same path/folder as the original .docm file).
--- This is saving the .pdf in the user's Document folder instead of the same folder as the .docm. ---
3. Silently attach the newly created .pdf file and email with Outlook (users might be on desktop version or web/mobile version)
--- Currently, the .docm file is attached and emailed rather than the .pdf. ---
4. MsgBox the user to confirm form submission.
--- This is working currently. ---

The final attachment has to be a .pdf because the files will be mailed to a MIcrosoft Team site and test documents I've sent do not work on Teams if they are macro-enabled. I'd also be fine if there was a way to skip the email and save the file directly to a specific Team File Folder or SharePoint folder but I haven't been able to work out how to set the correct paths. I set myself up with a Test Team site and uploaded .docm file and viewing the properties shows the following: 2410_Lacey_Test>Documents>Test>TestFile2.docm Oddly, when I uploaded the .docm file myself, I can open and view the file but if I email a .docm to the Team site the document is unusable (hence the need for the attachment to be a .pdf).

My current test file 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

Dim sPathUser As String
sPathUser = Environ$("USERPROFILE") & "\my documents\"

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 , Source = sPathUser & strFilename
    .Send
End With

Application.ScreenUpdating = True

MsgBox "Form Submitted", vbInformation

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

End Sub

Open in new window

ASKER CERTIFIED SOLUTION

Bill Prew

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Sydney Lacey

ASKER

Thank you for responding, Bill.

You are my VBA hero for today! Something so simple but it makes a huge difference for me.

Your solution gives me hope for my ability to eventually understand all this. Last night I was doing some "light" reading of my newly acquired VBA doorstop book and I was going to try the Environ$ function on my own today so at least i know I was headed in the right direction.

Now I can relax and just work my way through some tutorials and lessons. This one little minor project is all that we need for now... anything else I'm creating for the next few months would be very similar to this.

Sydney Lacey

ASKER

Very helpful! I appreciate the quick response and no judgement of my "newbieness." In the education field, we refer to "scaffolded learning" meaning giving just enough help to get the student to the next level. Mission accomplished!

Bill Prew

Great. glad that was helpful, and thanks for the kind feedback.

»bp