How To Send Outlook Email Using VBA

[mfaisalrazzak]

I wants to e-mail many peoples.

I have Excel file contain Name,email,Subject and Attachment

Regards,

[jja082]

you may use below script, change the "Button1_Click()" with you Macro Name, .Display is used to view the mail before sending.

Option Explicit

Sub Button1_Click()
'Working in 2000-2010
    Dim OutApp As Object
    Dim OutMail As Object
    Dim sh As Worksheet
    Dim cell As Range, FileCell As Range, rng As Range

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

    Set sh = Sheets("Sheet1")

    Set OutApp = CreateObject("Outlook.Application")

    For Each cell In sh.Columns("C").Cells.SpecialCells(xlCellTypeConstants)

        'Enter the file names in the D:Z column in each row
        Set rng = sh.Cells(cell.Row, 1).Range("D1:Z1")

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

            With OutMail
                .to = cell.Value
                .Subject = "Testmail"
                .Body = "Hi " & cell.Offset(0, -2).Value

                For Each FileCell In rng.SpecialCells(xlCellTypeConstants)
                    If Trim(FileCell) <> "" Then
                        If Dir(FileCell.Value) <> "" Then
                            .Attachments.Add FileCell.Value
                        End If
                    End If
                Next FileCell

                .Display  'Or use Send
            End With

            Set OutMail = Nothing
        End If
    Next cell

    Set OutApp = Nothing
    With Application
        .EnableEvents = True
        .ScreenUpdating = True
    End With
    
End Sub

[mfaisalrazzak]

Thanks.

but i received massege before sending every mail Allow / Deny .

[Excel Fox]

Did you check the template given here? http://www.excelfox.com/forum/f18/se...email-ids-304/

[Excel Fox]

Well, this is not recommended, but you can change the option of programmatic access in the trust center (Outlook 2007) to 'Never warn me about suspicious activity', and you should not get that message again.

[mfaisalrazzak]

Well, this is not recommended, but you can change the option of programmatic access in the trust center (Outlook 2007) to 'Never warn me about suspicious activity', and you should not get that message again.

Yes, but i want through VBA Code when needed

[jja082]

you can choose allow, then select 10 minutes, if you dont want to change the settings of the trust center, in that case, you are allowing to connect the MAcro atleast 10 minutes to your outlook, and never see that message for the next 10 minutes

[jaslake]

Hi mfaisalrazzak

What version of Excel are you using?

What version of Outlook are you using?

Post your workbook with the Code you've tried...we'll try to make it work.