#1
|
|||
|
|||
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 |
#2
|
||||
|
||||
Change:
.Attachments.Add Doc.FullName to: .Attachments.Add strFilename or: .Attachments.Add Split(Doc.FullName, ".doc")(0) & ".pdf"
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#3
|
|||
|
|||
Thanks so much!
Makes perfect sense now. Thanks for your help.
|
Tags |
.docm, .pdf, email attachment |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Email sends mailmerge file behind email rather than attachment | TLC1974 | Mail Merge | 2 | 07-22-2016 12:53 AM |
VBL code for automatic attachment of file into email | DrLoveday1 | Outlook | 1 | 04-12-2013 05:36 AM |
Command button - save in temp folder and send email with attachment | bigbird69 | Word VBA | 13 | 11-18-2012 10:06 PM |
A newbie question: a must to save macro word file as .docm? | tinfanide | Word VBA | 6 | 12-06-2011 03:02 PM |
email as pdf attachment - subject line and attachment named after mail merge | Nexus | Mail Merge | 12 | 04-13-2011 11:34 PM |