Email worksheet as PDF and using cells within activesheet for email addresses

**Mosborne1** · 02-23-2011, 12:15 PM

I am very new to coding. I found coding to convert and email a worksheet in pdf format at http://www.rondebruin.nl/files/PDFExamples.txt. I need to email these files to a few different addresses within a range of cells. It is too cumbersome to have to hard code each sheet within the workbook with email addresses from the active sheet I am emailing. Each sheet goes to a different customer. this is the code I am using:

Sub RDB_Worksheet_Or_Worksheets_To_PDF()
    Dim FileName As String

    If ActiveWindow.SelectedSheets.Count > 1 Then
        MsgBox "There is more than one sheet selected," & vbNewLine & _
               "and every selected sheet will be published."
    End If

    'Call the function with the correct arguments.
    'You can also use Sheets("Sheet3") instead of ActiveSheet in the code(the sheet does not need to be active then).
    'FileName = RDB_Create_PDF(ActiveSheet, "", True, True)

    'For a fixed file name and to overwrite it each time you run the macro, use the following statement.
    FileName = RDB_Create_PDF(ActiveSheet, "C:\Documents and Settings\mosborne\Desktop\FLL Fuel Margin Report.pdf", True, False)

    If FileName <> "" Then
        'Uncomment the following statement if you want to send the PDF by e-mail.
        
        RDB_Mail_PDF_Outlook FileName, "email@emailaddress.com;fsmith@email.com;jdoe@email.com", "FLL Fuel Margin Report", _
           "See the attached PDF file with updated figures" _
          & vbNewLine & vbNewLine & "Regards Jane Doe", False
    
    Kill FileName
    
    Else
        MsgBox "It is not possible to create the PDF; possible reasons:" & vbNewLine & _
               "Add-in is not installed" & vbNewLine & _
               "You canceled the GetSaveAsFilename dialog" & vbNewLine & _
               "The path to save the file is not correct" & vbNewLine & _
               "PDF file exists and you canceled overwriting it."
               
    End If
End Sub

I found coding to email the worksheet to a range of addresses within the page, but havent' been successful in using it for the pdf coding.:

Dim rng As Range
    Dim Arr() As String
    Dim N As Integer
    Dim cell As Range

    Set rng = ThisWorkbook.Sheets("Letter Template").Range("b13:B15")
    ReDim Preserve Arr(1 To rng.Cells.Count)
    N = 0
    For Each cell In rng
        If cell.Value Like "?*@?*.?*" Then
            N = N + 1
            Arr(N) = cell.Value
        End If
    Next cell
    ReDim Preserve Arr(1 To N)
    
    .SendMail Arr, "Margin Report"

Thank you.

**Mosborne1** · 02-24-2011, 04:55 PM

"Still looking for help on this, any advice?"

**johnkubik** · 02-24-2011, 06:13 PM

I use this in my email macro. I have a Sheet called "Email List" and in column "F" of that sheet is an email address. I have it so when there is an 'x' in any of the column "A" it adds the email list to a variable q, with the correct ; to make it so you can add multiple addresses.

        For Each cell In Sheets("Email List").Columns("F").Cells.SpecialCells(xlCellTypeConstants)
        If LCase(Sheets("Email List").Cells(cell.Row, "B").Value) = "x" Then

    q = q & "; " & cell.Value
    Else
    If LCase(Sheets("Email List").Cells(cell.Row, "B").Value) = "c" Then

    c = c & "; " & cell.Value
   
        End If
        End If
    Next cell

Oh yeah, the x adds it to the "to list" the c does the cc list.

If you want to see the semi-full macro look here...
http://www.excelforum.com/excel-prog...mac-macro.html

Hope this helps!