Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    May 2002
    Location
    Melbourne, Australia
    Posts
    269
    Thanks
    32
    Thanked 0 Times in 0 Posts

    Convert Word 2003 doc to PDF and email it using a macro

    Is it possible to use a macro to convert a current Word 2003 document to PDF and email it as an attachment using the installed email client (in this case Outlook Express)?

    In other words, automating the function in Word 2003 that currently exists in Word 2010, which enables the current document to be emailed as a PDF file.

    Does such a macro exist? If so, I would be very grateful if someone would share it with me.

    Thank you.


  2. #2
    Gold Lounger Roderunner's Avatar
    Join Date
    Dec 2009
    Location
    Scotland.
    Posts
    3,462
    Thanks
    16
    Thanked 216 Times in 183 Posts
    I don't have any office equipment installed but, I do use the built-in Wordpad and use CutePDF to make pdf's.
    O wad some Power the giftie gie us, to see oursels as ithers see us!

  3. #3
    3 Star Lounger
    Join Date
    May 2002
    Location
    Melbourne, Australia
    Posts
    269
    Thanks
    32
    Thanked 0 Times in 0 Posts
    Already using CutePDF to convert docs to PDF.

    What I am trying to do is to automate the current manual process of converting the doc to PDF, saving the file, running the email program and attaching the saved PDF file. Word 2010 does this process automatically and I was hoping for a macro to achieve this.

    Perhaps there is an add-in to Word 2003 that I am not aware of?

    Thank you.

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Useful,

    Here's some Access VBA code you could adapt. This code creates the annual billing statements for our homeowner's association and emails then to the owners who have indicated that they will recieve them via email. Note: It uses PDFCreator since it can be set to write to a file.
    Code:
    '                         +-------------------------+             +----------+
     '-------------------------|      EmailBills()       |-------------| 01/11/12 |
     '                         +-------------------------+             +----------+
     'Requires : PDFCreator {Open Source PDF Printer Driver}
     '           Sleep      {Windows API Function Declaration}
     'Called By: Switchboard
     'Calls: ClearPDFDirectory()
     '       SetDateForBills()
     '       [Utilities] SwitchPrinters()
     '       [Utilities] zGetDBPath()
    
     Sub EmailBills()
    
        Dim dbName     As Database
        Dim rst        As Recordset
        Dim lRecNo     As Long
        Dim lBillCnt   As Long
        Dim zWhere     As String
        Dim zMsgBody   As String
     #If LateBinding = 0 Then    'Early Binding
        Dim appOL      As Outlook.Application
        Dim miMail     As Outlook.mailitem
     #Else
        Dim appOL      As Object
        Dim miMail     As Object
     #End If
    
        Dim oMyAttach  As Object
        Dim zAttFN     As String
        Dim zBillPath  As String
       
        Forms![Switchboard].Visible = False
        If Not SetDateForBills() Then
          Forms![Switchboard].Visible = True
          Exit Sub
        End If
       
        MsgBox "Please Note:" & vbCrLf & vbCrLf & _
               "If Microsoft Outlook is Closed the created Emails " & vbCrLf & _
               "will be sent to the INBOX folder." & vbCrLf & vbCrLf & _
               "If Microsoft Outlook is OPEN {recommended} the created Emails " _
               & vbCrLf & "will be sent to the DRAFTS folder." & vbCrLf & vbCrLf & _
               "When OUTLOOK is properly set press OK", _
               vbOKOnly + vbInformation, _
               "IMPORTANT INFORMATION:"
              
        zBillPath = zGetDBPath() & "EmailBills\"
              
        ClearPDFDirectory
        strDfltPrt = Application.Printer.DeviceName
        SwitchPrinters "PDFCreator"
       
        Set appOL = CreateObject("Outlook.Application")
        Set dbName = CurrentDb()
        Set rst = dbName.OpenRecordset("Owners", dbOpenDynaset)
        rst.MoveFirst
       
        lBillCnt = 0
        zMsgBody = "Please find your WPOA annual dues statement attached." & _
                   vbCrLf & vbCrLf & "WOPA Board of Directors" & vbCrLf & _
                   vbCrLf & "Attachment: "
        Do
          If (rst![EMailDocs] And rst![EMail] <> "") Then
         
            zWhere = "[OwnerID] = " & Str(rst![OwnerID])
       
     'Note: If acNormal is selected the report is send automatically to the
     '      Default printer!
     '      If acPreview is selected the report is sent to the screen.
    
            DoCmd.OpenReport "rptAnnualBilling", acNormal, , zWhere
       
     '******* Rename file with OwnerID
    
     On Error GoTo WaitForPDFCreator
     Try_Again:
    
            Do While Dir(zBillPath & "rptAnnualBilling.pdf") = vbNullString
              Sleep 1250           '** wait 1.25 secs before trying again **
            Loop
           
            Name zBillPath & "rptAnnualBilling.pdf" As _
                 zBillPath & "Bill" & Format(rst![OwnerID]) & ".pdf"
     On Error GoTo 0
     '******* Begin Send Email
    
     #If LateBinding = 0 Then
            Set miMail = appOL.CreateItem(olMailItem)  '*** olMailItem = 0 ***
     #Else
            Set miMail = appOL.CreateItem(0)
     #End If
    
            With miMail
                .To = rst![EMail]
                .Subject = "WPOA Annual Dues Statement: " & rst![OwnerLName]
                .Body = zMsgBody & "Bill" & Trim(Str(rst![OwnerID])) & _
                        " Owner: " & rst![OwnerLName]
                .ReadReceiptRequested = True
                zAttFN = zBillPath & "Bill" & _
                         Trim(Str(rst![OwnerID])) & ".pdf"
                Set oMyAttach = miMail.Attachments.Add(zAttFN)
                .Save
            End With   'miMail
    
            Set miMail = Nothing
            lBillCnt = lBillCnt + 1  '*** Count Emails Created ***
    
     '******* End Send Email
    
          End If
         
          rst.MoveNext        '*** Move to Next Record ***
       
        Loop Until rst.EOF
       
        MsgBox Format(lBillCnt, "#,###") & " Email Bills Created." & _
               vbCrLf & vbCrLf & _
               "Maximize Outlook and Press F8 and select the" & _
               "SendAllDrafts macro then click Run." & _
               vbCrLf & vbCrLf & _
               "If Outlook wasn't open when you created the Email" & _
               vbCrLf & "Bills you will have to move them to the" & _
               vbCrLf & "Drafts folder from the Inbox BEFORE you" & _
               vbCrLf & "run the macro!", vbOKOnly + vbInformation, _
               "Next Step:"
        GoTo GetOut
    
     WaitForPDFCreator:
        Select Case Err.Number
              Case 75
                  Sleep 0.75  '*** Wait another 3/4 second. ***
                  Resume Try_Again
              Case Else
                  MsgBox "Module:" & vbTab & "BillingsCode" & vbCrLf & _
                         "Routine:" & vbTab & "EmailMailBills" & vbCrLf & _
                         "Error: " & Err.Number & " " & _
                         Err.Description, vbCritical + vbOKOnly, _
                         "Unexpected Error:"
                  Resume GetOut
        End Select
       
     GetOut:
        Set rst = Nothing     '*** Close RecordSet ***
        Set oMyAttach = Nothing
        Set miMail = Nothing
        Set appOL = Nothing
       
        SwitchPrinters strDfltPrt
        Forms![Switchboard].Visible = True
       
     End Sub                   '*** EmailBills() ***
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. #5
    3 Star Lounger
    Join Date
    May 2002
    Location
    Melbourne, Australia
    Posts
    269
    Thanks
    32
    Thanked 0 Times in 0 Posts
    Thank you RetiredGreek. I will have a look at the VBA code and see if I can work out how to adapt it. Wish me luck ... I'm not a programmer.

    It's a little frustrating that Microsoft has included the function in Word 2003 to send the current document as an email attachment, however, it does so without first converting it to a PDF file, hence the desire to automate the process.

    Thanks again.

  6. #6
    3 Star Lounger
    Join Date
    Apr 2012
    Posts
    240
    Thanks
    3
    Thanked 24 Times in 24 Posts
    I used Amyuni PDF converter for a long time. You could create a customized Amyuni "Printer" that would create the PDF and attach it to an e-mail.

    Doug Mac

  7. #7
    3 Star Lounger
    Join Date
    May 2002
    Location
    Melbourne, Australia
    Posts
    269
    Thanks
    32
    Thanked 0 Times in 0 Posts
    Thank you Doug Mac, I will investigate it.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •