Modify VBA to send email with attachment

OK I think I made some progress:

' adjust to whatever output folder you want
Const mc_strOUTPUT_PATH As String = "F:\Reports\"

Function RDB_Mail_PDF_Outlook(FileNamePDF As String, StrTo As String, StrSubject As String, StrBody As String, Send As Boolean)
    Dim OutApp As Object
    Dim OutMail As Object

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

    On Error Resume Next
    With OutMail
        .To = StrTo
        .CC = ""
        .BCC = ""
        .Subject = StrSubject
        .Body = StrBody
        .Attachments.Add FileNamePDF
        If Send = True Then
            .Send
        Else
            .Display
        End If
    End With
    On Error GoTo 0

    Set OutMail = Nothing
    Set OutApp = Nothing
End Function

Sub ReportPDF()
Dim lngLastRow As Long
    Dim lngRow As Long
    Dim wksCase As Worksheet
    Dim wksReport As Worksheet
    Dim rngOutput As Range
    Dim varMatch
    Dim FileName As String
    Dim EAddress As String
    Dim PName As String
    
    Set wksCase = Sheets("CaseCounter")
    Set wksReport = Sheets("Report")
    
    Set rngOutput = Sheets("Output").Range("A2")
    
    With wksCase
        lngLastRow = .Cells(.Rows.Count, "S").End(xlUp).Row
        For lngRow = 2 To lngLastRow
            If .Cells(lngRow, "S").Value > 0 Then
                ' populate feeder cell with value from column A
                varMatch = Application.Match(.Cells(lngRow, "A").Value, Sheets("Sheet2").Range("D:D"), 0)
                If Not IsError(varMatch) Then
                    EAddress = Sheets("Sheet2").Cells(varMatch, "A").Value
                varMatch = Application.Match(.Cells(lngRow, "A").Value, Sheets("Sheet2").Range("C:C"), 0)
                If Not IsError(varMatch) Then
                    PName = Sheets("Sheet2").Cells(varMatch, "A").Value
                varMatch = Application.Match(.Cells(lngRow, "A").Value, Sheets("Sheet2").Range("B:B"), 0)
                If Not IsError(varMatch) Then
                    rngOutput.Value = Sheets("Sheet2").Cells(varMatch, "A").Value
                    Do While Application.CalculationState = xlCalculating
                        DoEvents
                    Loop
                    wksReport.ExportAsFixedFormat xlTypePDF, mc_strOUTPUT_PATH & rngOutput.Value & "-June12" & ".pdf"
                    FileName = rngOutput.Value & "-June12"
                ' send email here
                    RDB_Mail_PDF_Outlook FileName, EAddress, "June 2012", _
                             "Dear " & PName & "," & vbNewLine & "Please find your personal report for the month of June attached." & vbNewLine & _
                           "Best wishes" & vbNewLine & "dip11", False
                Else
                    MsgBox "Cannot find person code " & .Cells(lngRow, "A").Value
                End If
            End If
        Next lngRow
    End With
End Sub

But for some strange reason I now keeps throwing an error "Compile Error: Next without for"

Considering it used to be happy with the Next without the added email code, what is causing this and how do I fix it?

Ahhhhh, thanks for that, didn't notice!

Now this kind of works, except that it brings up the Outlook new message dialog without "To", "Name" and "Attachment"
So essentially it is not using the EAddress, PName and FileName/FileNamePDF strings to fill those in and send the email :/

Any ideas?

its not converting to pdf ...

It is converting to pdf just fine - I can see the pdfs being produced in the folder.

Can u share the code how the reports are generating in pdf

I've managed to fix it myself.

Turns out I was using application.match incorrectly and should have used range B:B and jsut mod the number after the comma. And for filename I just needed to add the filepath to the filename...

For some reason this doesn't work properly anymore even though I haven't changed anything (other than reboot my PC).

It seems to use the rng value for PName and generate some random email address (I get bounce backs for failed senders) from my organization.

' adjust to whatever output folder you want
Const mc_strOUTPUT_PATH As String = "F:\Reports\"

Function RDB_Mail_PDF_Outlook(FileNamePDF As String, StrTo As String, StrSubject As String, StrBody As String, Send As Boolean)
    Dim OutApp As Object
    Dim OutMail As Object

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

    On Error Resume Next
    With OutMail
        .To = StrTo
        .CC = ""
        .BCC = ""
        .Subject = StrSubject
        .Body = StrBody
        .Attachments.Add FileNamePDF
        If Send = True Then
            .Send
        Else
            .Display
        End If
    End With
    On Error GoTo 0

    Set OutMail = Nothing
    Set OutApp = Nothing
End Function

Sub ReportPDF()
Dim lngLastRow As Long
    Dim lngRow As Long
    Dim wksCase As Worksheet
    Dim wksReport As Worksheet
    Dim rngOutput As Range
    Dim varMatch
    Dim FileName As String
    Dim EAddress As String
    Dim PName As String
    
    Set wksCase = Sheets("CaseCounter")
    Set wksReport = Sheets("Report")
    
    Set rngOutput = Sheets("Output").Range("A2")
    
    With wksCase
        lngLastRow = .Cells(.Rows.Count, "S").End(xlUp).Row
        For lngRow = 2 To lngLastRow
            If .Cells(lngRow, "S").Value > 0 Then
                ' populate feeder cell with value from column A
                varMatch = Application.Match(.Cells(lngRow, "A").Value, Sheets("Sheet2").Range("B:B"), 3)
                If Not IsError(varMatch) Then
                    EAddress = Sheets("Sheet2").Cells(varMatch, "A").Value
                End If
                varMatch = Application.Match(.Cells(lngRow, "A").Value, Sheets("Sheet2").Range("B:B"), 2)
                If Not IsError(varMatch) Then
                    PName = Sheets("Sheet2").Cells(varMatch, "A").Value
                End If
                varMatch = Application.Match(.Cells(lngRow, "A").Value, Sheets("Sheet2").Range("B:B"), 0)
                If Not IsError(varMatch) Then
                    rngOutput.Value = Sheets("Sheet2").Cells(varMatch, "A").Value
                    Do While Application.CalculationState = xlCalculating
                        DoEvents
                    Loop
                    wksReport.ExportAsFixedFormat xlTypePDF, mc_strOUTPUT_PATH & rngOutput.Value & "-June12" & ".pdf"
                    FileName = rngOutput.Value & "-June12"
                ' send email here
                    RDB_Mail_PDF_Outlook FileName, EAddress, "June 2012", _
                             "Dear " & PName & "," & vbNewLine & "Please find your personal report for the month of June attached." & vbNewLine & _
                           "Best wishes" & vbNewLine & "dip11", False
                Else
                    MsgBox "Cannot find person code " & .Cells(lngRow, "A").Value
                End If
            End If
        Next lngRow
    End With
End Sub