Solved: Send an outlook email by reading from cell values

[Arun]

Hello Gurus,

I am in need of sending emails from excel , where i have two columns A have person's name , column B have email id of person and column c with score of person. (attached is sample excel)

I want to send an email to every person listed in the sheet such that subject is a template - "<<A>> your score is <<c>> for the Quiz" (i.e. Arun your score is 20 for the Quiz" by reading the values from row 1 to 200 with the use of VBA/Macro.

Plz help me.

Thanks in advance. please help.

[Bob Phillips]

[vba]

Sub SendMail()
Dim oOutlook As Object
Dim oMailItem As Object
Dim oRecipient As Object
Dim oNameSpace As Object
Dim lastrow As Long
Dim i As Long

Set oOutlook = CreateObject("Outlook.Application")
Set oNameSpace = oOutlook.GetNameSpace("MAPI")
oNameSpace.Logon , , True

lastrow = Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To lastrow

Set oMailItem = oOutlook.CreateItem(0)

With oMailItem

Set oRecipient = .Recipients.Add(Cells(i, "B").Value2)
oRecipient.Type = 1 '1 = To, use 2 for cc
.Subject = Cells(i, "A").Value2 & " your score for the quiz is " & Cells(i, "C").Value
.Display
End With
Next i
End Sub[/vba]

[Arun]

[vba]

Sub SendMail()
Dim oOutlook As Object
Dim oMailItem As Object
Dim oRecipient As Object
Dim oNameSpace As Object
Dim lastrow As Long
Dim i As Long

Set oOutlook = CreateObject("Outlook.Application")
Set oNameSpace = oOutlook.GetNameSpace("MAPI")
oNameSpace.Logon , , True

lastrow = Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To lastrow

Set oMailItem = oOutlook.CreateItem(0)

With oMailItem

Set oRecipient = .Recipients.Add(Cells(i, "B").Value2)
oRecipient.Type = 1 '1 = To, use 2 for cc
.Subject = Cells(i, "A").Value2 & " your score for the quiz is " & Cells(i, "C").Value
.Display
End With
Next i
End Sub[/vba]

Hello xld,

Thanks a lot for quick response , i am really thankful to you. Yes this is what actually i was looking forward to.
But one additional thing , if i run this then outlook mail gets opened but in composed format only and it don't send the mail actually, and i need to actually click on send for each and window. Is there any way by running this code it automatically sends the emails also?

Thanks a ton again.......
Arun

[Bob Phillips]

Change the line that says

.Display

to

.Send

That is testing mode, I should have added a comment on how to finalise it.

[Arun]

Hello xld,

Thanks a lot for quick response , i am really thankful to you. Yes this is what actually i was looking forward to.
But one additional thing , if i run this then outlook mail gets opened but in composed format only and it don't send the mail actually, and i need to actually click on send for each and window. Is there any way by running this code it automatically sends the emails also?

Thanks a ton again.......
Arun

Thanks for all your help xld, i just replaced .Display with .Send and it worked for me .... thanks a lot and i truely appericate your effort and help.

Arun

[Arun]

Hello Gurus,

Need your help on the below issues which currently i am facing -

1- Whenever i run the marcro then a Popup window comes up asking me to grant permission and need to manually click on 'Yes' button again and again.
But as i have to send more than 200 mails and clicking on every time require lots of time. Is there any way i can skip this and mails directly gets shooted to the users.

2- If in email refrence column there is some data not in correct format (i.e. 88653 in place of xyz@abc.com) then it stop running the macro and sending mails.
If there any way that whatever is entered in the email refrenced column, outlook sends an email to the same and if it will bonce then let it be.

xld please help me ....need your guys valuable feedback to overcome this ...thanks for all of your time and help in advance.

Thanks,
Arun

[Bob Phillips]

1) That is an Outlook security issue. Luckily, someone has already solved the problem - get hold of an Outlook addin called Redemption at http://www.dimastr.com/redemption/home.htm and install that

2) This code will check the email address for validity and send it if okay, else it will ignore it

[vba]

Option Explicit

Sub SendMail()
Dim oOutlook As Object
Dim oMailItem As Object
Dim oRecipient As Object
Dim oNameSpace As Object
Dim lastrow As Long
Dim i As Long

Set oOutlook = CreateObject("Outlook.Application")
Set oNameSpace = oOutlook.GetNameSpace("MAPI")
oNameSpace.Logon , , True

lastrow = Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To lastrow

Set oMailItem = oOutlook.CreateItem(0)

With oMailItem

If ValidEmail(Cells(i, "B").Value2) Then

Set oRecipient = .Recipients.Add(Cells(i, "B").Value2)
oRecipient.Type = 1 '1 = To, use 2 for cc
.Subject = Cells(i, "A").Value2 & " your score for the quiz is " & Cells(i, "C").Value
.send
End If
End With
Next i
End Sub

'-----------------------------------------------------------------
Public Function ValidEmail(Adress As String) As Boolean
'-----------------------------------------------------------------
Dim oRegEx As Object
Set oRegEx = CreateObject("VBScript.RegExp")
With oRegEx
.Pattern = "^[\w-\.]{1,}\@([\da-zA-Z-]{1,}\.){1,}[\da-zA-Z-]{2,3}$"
ValidEmail = .Test(Adress)
End With
Set oRegEx = Nothing
End Function
[/vba]

[Arun]

xld, vov this is awesome and i am really thankful to you and really appreciate your help and feedback.

Sure there is much more to learn and senior and knowledgeable person like you are an example...thanks for helping me out.

Arun