Auto email with excel

K

Ks1102

Well-known Member
Joined
Jan 8, 2008
Messages
689
Hi, I really hope someone can help!

I've got an excel listed about 4000 entries where column E is an email address, coumn A is a contact person, column B is a customer A/C , column C relates the event issued, and column D is the date effective, I need to send the notification mail looking if VBA can help.

want Dear in front with column contact.
next row is customer A/C
then the event is the body.
last is the date issuing.


can some one help me the code!

highly appreciate the help
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
Sort by date Sort by votes
Along with the link given you could try adapting this:
This will mail all addresses in column E.
It uses column offsets to the email address to locate the contents - so check they are right. Run it through manually until you get what you want.
It only displays the mail so when you are ready comment out display instead of send.

Code: 
Sub Message_many()
Dim OutApp As Object
Dim OutMail As Object
Dim EmailSubject As String
Dim EmailSendTo As String
Dim MailBody As String
 
'Subject string
EmailSubject = "Notification"
Sheets("Sheet1").Activate
Range("E1").Select
 
Do While ActiveCell.Value <> ""
EmailSendTo = ActiveCell.Value
 
MailBody = "Dear " & ActiveCell.Offset(0, -4) _
& vbCr & "A/C: " & ActiveCell.Offset(0, -3) _
& vbCr & vbCr _
& ActiveCell.Offset(0, -2) & vbCr & vbCr _
& "issue date: " & ActiveCell.Offset(0, -1)
 
 
ActiveCell.Offset(1, 0).Select
 
 
'Send Mail
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(o)
With OutMail
.Subject = EmailSubject
.To = EmailSendTo
.Body = MailBody
.Display
'.send
End With
 
Set OutMail = Nothing
Set OutApp = Nothing
MailBody = ""
Loop
End Sub
 
Upvote 0
daverunt said:
Along with the link given you could try adapting this:
This will mail all addresses in column E.
It uses column offsets to the email address to locate the contents - so check they are right. Run it through manually until you get what you want.
It only displays the mail so when you are ready comment out display instead of send.

Code: 
Sub Message_many()
Dim OutApp As Object
Dim OutMail As Object
Dim EmailSubject As String
Dim EmailSendTo As String
Dim MailBody As String
 
'Subject string
EmailSubject = "Notification"
Sheets("Sheet1").Activate
Range("E1").Select
 
Do While ActiveCell.Value <> ""
EmailSendTo = ActiveCell.Value
 
MailBody = "Dear " & ActiveCell.Offset(0, -4) _
& vbCr & "A/C: " & ActiveCell.Offset(0, -3) _
& vbCr & vbCr _
& ActiveCell.Offset(0, -2) & vbCr & vbCr _
& "issue date: " & ActiveCell.Offset(0, -1)
 
 
ActiveCell.Offset(1, 0).Select
 
 
'Send Mail
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(o)
With OutMail
.Subject = EmailSubject
.To = EmailSendTo
.Body = MailBody
.Display
'.send
End With
 
Set OutMail = Nothing
Set OutApp = Nothing
MailBody = ""
Loop
End Sub
Click to expand...

Thanks help it was work perfectly.
 
Upvote 0
daverunt said:
Along with the link given you could try adapting this:
This will mail all addresses in column E.
It uses column offsets to the email address to locate the contents - so check they are right. Run it through manually until you get what you want.
It only displays the mail so when you are ready comment out display instead of send.

Code: 
Sub Message_many()
Dim OutApp As Object
Dim OutMail As Object
Dim EmailSubject As String
Dim EmailSendTo As String
Dim MailBody As String
 
'Subject string
EmailSubject = "Notification"
Sheets("Sheet1").Activate
Range("E1").Select
 
Do While ActiveCell.Value <> ""
EmailSendTo = ActiveCell.Value
 
MailBody = "Dear " & ActiveCell.Offset(0, -4) _
& vbCr & "A/C: " & ActiveCell.Offset(0, -3) _
& vbCr & vbCr _
& ActiveCell.Offset(0, -2) & vbCr & vbCr _
& "issue date: " & ActiveCell.Offset(0, -1)
 
 
ActiveCell.Offset(1, 0).Select
 
 
'Send Mail
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(o)
With OutMail
.Subject = EmailSubject
.To = EmailSendTo
.Body = MailBody
.Display
'.send
End With
 
Set OutMail = Nothing
Set OutApp = Nothing
MailBody = ""
Loop
End Sub
Click to expand...


Hi, daverunt

how do skip the alert which say !! the program is trying to automatically send e-mail on your behalf, Do you want to allow it ?

do any way send directly without alert since the listed about 4000 entries.pleased.
 
Upvote 0
If you can't get rid of the Outlook warning you can try the CDO method.
Read Rons link above - tells you all about it.

You must change entries below:
smtp server to the one your email uses.
.from entries

Outlook is not required for this method so you don't get security warnings.
Internet connection must be active.
You don't get any visual that it has been sent.
You could .bcc yourself but in your case you'd end up with 4000 mails!

Code: 
Sub MessageMany_UsingCDO()
'Avoids Outlook security
'Requires active internet connection
 
Dim iCfg As CDO.Configuration
Dim iMsg As CDO.Message
'Subject string
EmailSubject = "Notification"
Sheets("Sheet1").Activate
Range("E1").Select
 
Do While ActiveCell.Value <> ""
EmailSendTo = ActiveCell.Value
MailBody = "Dear " & ActiveCell.Offset(0, -4) _
& vbCr & "A/C: " & ActiveCell.Offset(0, -3) _
& vbCr & vbCr _
& ActiveCell.Offset(0, -2) & vbCr & vbCr _
& "issue date: " & ActiveCell.Offset(0, -1)
ActiveCell.Offset(1, 0).Select
 
Set iCfg = New CDO.Configuration
With iCfg
.Fields(cdoSMTPServer) = "smtp.tiscali.co.uk" ''''''''''''Change this
.Fields(cdoSMTPServerPort) = 25 ' Usual setting
.Fields(cdoSendUsingMethod) = cdoSendUsingPort
.Fields(cdoSMTPConnectionTimeout) = 200
.Fields.Update
End With
Set iMsg = New CDO.Message
With iMsg
Set .Configuration = iCfg
.Sender = """Fred"" <[EMAIL="fred@yahoo.co.uk"]fred@yahoo.co.uk[/EMAIL]>" '''''''''''''''Change this
.Subject = EmailSubject
.TextBody = MailBody
.To = EmailSendTo
'.bcc
.send
End With
Set iMsg = Nothing
Set iCfg = Nothing
Loop
 
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question Question
How to add a button in Excel to send custom email
Replies
0
Views
163
just4jeffrey
J
N
  • Question Question
Email from Excel
Replies
2
Views
391
Nyge2k3
N
O
  • Question Question
Auto email code
Replies
3
Views
275
Logit
Logit
A
  • Solved
Copy Entire Workbook, save as sheet name with current date, hide all sheets except one, & attach to email
Replies
2
Views
614
Amo840812
A
F
  • Solved
vba send email by xloo, questions.
Replies
2
Views
319
francosueho82
F

Forum statistics

Threads
1,224,591
Messages
6,179,768
Members
452,940
Latest member
rootytrip

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top