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

[Excel Fox]

Did you check the template given here? http://www.excelfox.com/forum/f18/send-outlook-mail-signature-range-excel-multiple-email-ids-304/

[mfaisalrazzak]

Thanks.

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

[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.