Need a macro to send email to multiple email id given in a column

[kk1990]

Hi,

I am new to macros.
I need to macro which can be assigned to a command button and on clicking the button email should be sent to mail id as per below criteria.
The macro should check a column (Column G, it contains formula). If the value in column G is "Not updated" (which is basically an if formula to give output as "Updated" or "Not updated" as per date of last update), macro should take its corresponding email id (present in column F) and send mail with mail body as
"Update the project chart for" & column C (contains project name) & column D (contains SAP code) & "which was last updated on" & column I (contains date of last update).

Macro should check whole of column G and not just one cell.

Please help as i am totally new to macros!

 

[Hui]

KK1990

Firstly, Welcome to the Chandoo.org Forums

This question is one of the most regularly asked questions

Try using the Search Box at the Top right of this and every page at Chandoo.org

 

[kk1990]

@Hui

I tried that and got a result - http://chandoo.org/forum/threads/re...details-in-cells-using-vba.20403/#post-122808

I am afraid to say but this is not working.

Thanks

 

[Hui]

KK1990

Next I suggest you read the rules of the forum at:
http://chandoo.org/forum/threads/new-users-please-read.294/

With regard to your question we have no idea what data you have, or what you are trying to do.

Posting a sample file is strongly recommended:

 

[kk1990]

Thanks @Hui !

Here is sample of excel with VBA code. In column D, if status is - "not updated", an email should be sent to corresponding mail id present in column C.

I hope I am clear now.

Best regards

 

[Dayanand]

Hi KK1990,

Use below code, just i removed Lcase and its working fine..

Sub CommandButton1_Click()
Dim OutApp As Object
Dim OutMail As Object
Dim cell As Range

Application.ScreenUpdating = False
Set OutApp = CreateObject("Outlook.Application")

On Error GoTo cleanup
For Each cell In Columns("C").Cells.SpecialCells(xlCellTypeConstants)
If cell.Value Like "?*@?*.?*" And _
(Cells(cell.Row, "D").Value) = "Not updated" Then

Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
With OutMail
.To = cell.Value
.Subject = "Reminder"
.Body = "Dear " & Cells(cell.Row, "B").Value _
& vbNewLine & vbNewLine & _
"Please contact us to discuss bringing " & _
"your account up to date"
'You can also add files like this:
'.Attachments.Add ("C:\test.txt")
.Send 'Or use Display.
End With
On Error GoTo 0
Set OutMail = Nothing
End If
Next cell

cleanup:
Set OutApp = Nothing
Application.ScreenUpdating = True
End Sub

 

[kk1990]

Hi KK1990,

Use below code, just i removed Lcase and its working fine..

Sub CommandButton1_Click()
Dim OutApp As Object
Dim OutMail As Object
Dim cell As Range

Application.ScreenUpdating = False
Set OutApp = CreateObject("Outlook.Application")

On Error GoTo cleanup
For Each cell In Columns("C").Cells.SpecialCells(xlCellTypeConstants)
If cell.Value Like "?*@?*.?*" And _
(Cells(cell.Row, "D").Value) = "Not updated" Then

Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
With OutMail
.To = cell.Value
.Subject = "Reminder"
.Body = "Dear " & Cells(cell.Row, "B").Value _
& vbNewLine & vbNewLine & _
"Please contact us to discuss bringing " & _
"your account up to date"
'You can also add files like this:
'.Attachments.Add ("C:\test.txt")
.Send 'Or use Display.
End With
On Error GoTo 0
Set OutMail = Nothing
End If
Next cell

cleanup:
Set OutApp = Nothing
Application.ScreenUpdating = True
End Sub

Hi KK1990,

Use below code, just i removed Lcase and its working fine..

Sub CommandButton1_Click()
Dim OutApp As Object
Dim OutMail As Object
Dim cell As Range

Application.ScreenUpdating = False
Set OutApp = CreateObject("Outlook.Application")

On Error GoTo cleanup
For Each cell In Columns("C").Cells.SpecialCells(xlCellTypeConstants)
If cell.Value Like "?*@?*.?*" And _
(Cells(cell.Row, "D").Value) = "Not updated" Then

Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
With OutMail
.To = cell.Value
.Subject = "Reminder"
.Body = "Dear " & Cells(cell.Row, "B").Value _
& vbNewLine & vbNewLine & _
"Please contact us to discuss bringing " & _
"your account up to date"
'You can also add files like this:
'.Attachments.Add ("C:\test.txt")
.Send 'Or use Display.
End With
On Error GoTo 0
Set OutMail = Nothing
End If
Next cell

cleanup:
Set OutApp = Nothing
Application.ScreenUpdating = True
End Sub

Thanks a lot @Dayanand

You made my day !!