Excel VBA code to write Print Area as PDF and email

  • Excel VBA code to write Print Area as PDF and email
    Hello, I have created a excel workbook that uses =Indirect(“Data!A &RowIndex”) to pull data through into my news letter layout.

    In my news letter I have:
    Member First Name in cell (Data!A1 &RowIndex)
    Member Last Name in cell (Data!B1 &RowIndex)
    Arrears in cell (Data!C1 &RowIndex)
    Dues in cell (Data!D1 &RowIndex)
    Total in cell (Data!E1 &RowIndex)
    Email address in cell (Data!F1 &RowIndex)
    The email body in Form cells B2:I48
    the email subject is “Monthly Meeting”

    I have a list of members’ Names in a worksheet called "Data" in Columns A & B

    The email address is also in “Form” G5 if it is easier to extract from there.

    The formula =Indirect(“xxx &RowIndex”) updates each newsletter and my current code produces an email with Print Range embedded in the news letter. I previously adapted a routine that printed the newsletter for each member to be mailed out. It can be emailed, but manually. I am just looking to amend this code to print to PDF, and then send the emails automatically.

    It is currently locked to two outputs during the testing phase. This is shown on the “Form” sheet at cell “C5”.

    Here is my current code:

    Public Const APPNAME As String = "Sample-1"
    Option Explicit

    Sub PrintForms()
    Dim StartRow As Integer
    Dim EndRow As Integer
    Dim Msg As String
    Dim MailDest As String
    Dim i As Integer

    Dim OutApp As Object
    Dim OutMail As Object
    Dim strbody As String

    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)

    StartRow = Range("StartRow")
    EndRow = Range("EndRow")

    If StartRow > EndRow Then
    Msg = "ERROR" & vbCrLf & "The starting row must be less than the ending row!"
    MsgBox Msg, vbCritical, APPNAME
    End If

    For i = StartRow To EndRow
    Range("RowIndex") = i
    ActiveWorkbook.EnvelopeVisible = True
    With ActiveSheet.MailEnvelope
    '.Introduction = "This is a sample worksheet."
    .Item.to = "(email addresses here)"
    .Item.Subject = "Monthly Meeting"
    End With
    Next i
    End Sub

  • Thanks Carim. Nesting loops in VBA is beyond me. I usually hunt on line for something readily adapted, as in my example where I altered the output from hard printing to email. This is why I requested online help.
    While I have a mental picture of what needs to be done I don't know how to do it.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!