Thread: auto mailing with attachment

Hi friends,

I am very new outlook programming , i am think of automating few of my mails. i ahve folder in D:\Pdf where it contains lots of pdf with names , and fro that names , i have emails ids in excel . when could run a macro in excel can it refer to fiel name and pick the adress from the excel file and send the mail correspondingly , let me know whether this is possible.

format in excel
Sl No Producer Name To Mail address Cc Mail address
1 Ajgallagher Shon_OFallon@ajg.com
2 Ajg-glendale Mark_Smith-LA@ajg.com

if you see here producer name "ajgallagher" and my attachment pdf file will also have the same name

thanks in advance for your ideas

Hi,

under producer name you'll need to specifaid whole path, not just file name. As per bottom VBA ...

| SI No | Producer Name | To | Cc |
| 1 | D:\Pdf\Ajgallagher.pdf | Shon_OFallon@ajg.com | info@smth.bin |

Sub Send_PDFs()
    Dim OutApp As Object
    Dim OutMail As Object
    Dim sheet As Worksheet
    Dim cell_to As Range, PDFCell As Range, range As Range

    With Application
        .EnableEvents = False
        .ScreenUpdating = False
    End With

    Set sheet = Sheets("Sheet1")

    Set OutApp = CreateObject("Outlook.Application")
    
    For Each cell_to In sheet.Columns("C").Cells.SpecialCells(xlCellTypeConstants)

    'Enter the file names in the B column in each row
    Set range = sheet.Cells(cell_to.Row, 1).Range("B1:B1")

    If cell_to.Value Like "?*@?*.?*" And _
        Application.WorksheetFunction.CountA(rng) > 0 Then
        Set OutMail = OutApp.CreateItem(0)

        With OutMail
            .To = cell_to.Value
            'Cc = "info@smth.bin"
            .Subject = "Email Subject"
            .body = "I'm sending you ..."
                
            For Each FileCell In range.SpecialCells(xlCellTypeConstants)
                If Trim(FileCell) <> "" Then
                    If Dir(FileCell.Value) <> "" Then
                        .Attachments.Add FileCell.Value
                    End If
                End If
             Next FileCell
                
             '.Send of .Display
             .Display
        End With

        Set OutMail = Nothing
        End If

    Next cell_to

    Set OutApp = Nothing

    With Application
        .EnableEvents = True
        .ScreenUpdating = True
    
    End With

End Sub

... play with this. You need to add Cc field. i'm using id directly in VBA. Hope this helps.


Cheers, Marko