Send Email Macro

[Abhijeet]

Hi
I have data in excel for sending email so macro run then send emails to peoples what ever in excel file list email should be send through macro.Please help me

 

[rumshar]

Hi,
This is not coded by me, but found it to be very useful. Hence attaching for you.
With Regards
Rudra

 

[Abhijeet]

This macro not work in my system is their any setting need to change

 

[Afarag]

@Abhijeet

you can get your question answer from the below link

http://www.rondebruin.nl/win/s1/outlook/amail2.htm

Regards,

 

[rumshar]

This macro not work in my system is their any setting need to change

Not sure what type of error you are getting. Have you made reference to outlook by doing
VBA=>Tools=>Reference=>Microsoft Outlook(15.0) Object Library(you may not get 15.0 if you are not using office 2013)
With Regards
Rudra

 

[Abhijeet]

Its not opening because this file is password protected

 

[Afarag]

@Abhijeet,

you have to check your sheet after answering.

Regards,

 

[Abhijeet]

This is outlook Email right now i dont have outlook can send Yahoo mail send

 

[Afarag]

firstly go to VBA=>Tools=>Reference then click mark box Microsoft CDO for Windows 2000 Library

then try this code for yahoo and gmail configuration

code:

Public Function SendEmailUsingOther(strSendTo As String, strSubject As String, MailType As String, fld As String, Optional tempfile, Optional attch1, Optional attch2, Optional attch3)

MailSent = False
Dim NewMail As CDO.Message

Set NewMail = New CDO.Message
‘Set myMail = CreateObject(“CDO.Message”)

‘Enable SSL Authentication
NewMail.Configuration.Fields.Item _
(“http://schemas.microsoft.com/cdo/configuration/smtpusessl”) = True

‘Make SMTP authentication Enabled=true (1)

NewMail.Configuration.Fields.Item _
(“http://schemas.microsoft.com/cdo/configuration/smtpauthenticate”) = 1

‘Set the SMTP server and port Details
‘To get these details you can get on Settings Page of your Gmail Account

Select Case MailType
Case “yahoo”
NewMail.Configuration.Fields.Item _
(“http://schemas.microsoft.com/cdo/configuration/smtpserverport”) = 465
NewMail.Configuration.Fields.Item _
(“http://schemas.microsoft.com/cdo/configuration/smtpserver”) = “smtp.mail.yahoo.com”
Case “gmail”
NewMail.Configuration.Fields.Item _
(“http://schemas.microsoft.com/cdo/configuration/smtpserverport”) = 465
NewMail.Configuration.Fields.Item _
(“http://schemas.microsoft.com/cdo/configuration/smtpserver”) = “smtp.gmail.com”

End Select
NewMail.Configuration.Fields.Item _
(“http://schemas.microsoft.com/cdo/configuration/sendusing”) = 2

‘Set your credentials of your Gmail Account
SenderEmail = InputBox(“Please enter your email address”)
NewMail.Configuration.Fields.Item _
(“http://schemas.microsoft.com/cdo/configuration/sendusername”) = SenderEmail

NewMail.Configuration.Fields.Item _
(“http://schemas.microsoft.com/cdo/configuration/sendpassword”) = InputBox(“Please enter your password”)

‘Update the configuration fields
NewMail.Configuration.Fields.Update

NewMail.Configuration.Fields.Item(“http://schemas.microsoft.com/cdo/configuration/smtpusessl”) = “true”

‘Set All Email Properties

‘##########################”"
‘Open the HTML file using the FilesystemObject into a TextStream object
Set FSObj = New Scripting.FileSystemObject
Set TStream = FSObj.OpenTextFile(fld, ForReading)
‘Now set the HTMLBody property of the message to the text contained in the TextStream object
strHTML = TStream.ReadAll
‘################################

With NewMail
.Subject = [G2].Value
.From = SenderEmail
.to = [A2].Value
.CC = [B2].Value
.BCC = [C2].Value
.TextBody = vbCrLf & "Hi," _
& vbCrLf & vbCrLf & [i3].Value & "." _
& vbCrLf & [i4].Value _
& vbCrLf & vbCrLf & "Regards," _
& vbCrLf & "Amol"
.HTMLBody = strHTML
.Attachments.Add Destwb.FullName
'You can add other files also like this
.Attachments.Add ([E2].Value)
.Display 'or use .Send

If tempfile “” Then
.AddAttachment tempfile
End If
If Trim(attch1) “” Then
.AddAttachment attch1
End If
If Trim(attch2) “” Then
.AddAttachment attch2
End If
If Trim(attch3) “” Then
.AddAttachment attch3
End If
End With
‘MsgBox (strHTML)
NewMail.Send
‘MsgBox (“Mail has been Sent”)
MailSent = True
pbsend:
If MailSent = False Then
MsgBox (“Due to an issue (password, email address,attachments…), the email hasn’t been sent”)
End If
‘Set the NewMail Variable to Nothing
Set NewMail = Nothing

End Function

 

[Abhijeet]

Please give me macro file for this i will copy paste but not work.I change setting in VBA=>Tools=>Reference then click mark box Microsoft CDO for Windows 2000 Library

 

[SirJB7]

@Afarag
Hi!
When posting code do it in the proper window pane (5th icon from the right of the ribbon bar) or use the shortcut indicated just over that bar in the Reply text box.
Regards!

@Abhijeet
Hi!
I was yet missing your motto:
http://chandoo.org/forum/threads/i-want-birthday-reminder.14360/#post-85510
Regards!