VBA to loop through rows sending email

Z

Zhaki

New Member
Joined
Oct 5, 2017
Messages
11
Hey,

i have entered the following code and have absolutely no trouble executing, however, i dont know how to move it to the next row and perform the macro again. it basically is for sending emails....also i would like to update the excel sheet once the email is sent automatically...to say email sent.
Code: 
Sub Email_Notification()

Dim OutlookApp As Object
Dim OutlookMail As Object
Dim ToName As String
Dim CCName As String
Dim SendEmail As Integer
Dim cell As Range

ToName = Range("N9").Value
CCName = Range("P9").Value
mAttName = Range("C9").Value

Application.ScreenUpdating = False



SendEmail = MsgBox("Do you want to send the following Email?" _
& vbNewLine & vbNewLine & "Subject: " & SubName & vbNewLine & vbNewLine & _
"To: " & ToName & vbNewLine & _
"CC: " & CCName & vbNewLine & _
"With the following Attachments: " & vbNewLine & Range("C9").Value, _
vbQuestion + vbYesNo, "Send Email")


If SendEmail = vbYes Then

End If

Set OutlookApp = CreateObject("Outlook.application")
Set OutlookMailitem = OutlookApp.CreateItem(0)

With OutlookMailitem
.Display
.To = Range("N9").Value
.CC = Range("P9").Value
.Subject = "TMT-KSD File Review Request"
.HTMLBody = Body & Attachment
.Body = "Kindly review the attached file for TMT-KSD upload." & vbNewLine & "Regards."
.Attachments.Add Range("L9").Value
.send
 
End With

Set OutlookMail = Nothing
Set OutlookApp = Nothing
Application.ScreenUpdating = True



End Sub
your help would be greatly appreciated.
 
Last edited by a moderator:

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
Sort by date Sort by votes
Re: issues with VBA, please help!

Welcome to the board. You have used variables you haven't declared. At the very top of your module, add line "Option Explicit" this will highlight variables missing.

Try:
Rich (BB code): 
Sub Email_Notification()

    Dim OutlookApp  As Object
    Dim OutlookMail As Object
    Dim ToName      As String
    Dim CCName      As String
    Dim cell        As Range
    Dim strMsg      As String
    
    Dim x           As Long
    Dim LR          As Long
    
    Application.ScreenUpdating = False
    
    LR = Cells(Rows.count, 14).End(xlUp).row
    For x = 9 To LR
    
        ToName = Range("N" & x).Value
        CCName = Range("P" & x).Value
        mattname = Range("C" & x).Value
            
        strMsg = "Do you want to send the following Email?@1@1Subject: " & SubName & _
            "@1@1To: " & ToName & "@1CC: " & CCName & _
            "@1With the following Attachments: @1" & mattname
        strMsg = Replace(strMsg, "@1", vbCrLf)
                    
        If MsgBox(strMsg, vbQuestion + vbYesNo, "Send Email") = vbYes Then
        
            Set OutlookApp = CreateObject("Outlook.application")
            Set OutlookMailitem = OutlookApp.CreateItem(0)
            
            With OutlookMailitem
                .Display
                .to = ToName
                .CC = CCName
                .Subject = "TMT-KSD File Review Request"
                .HTMLBody = Body & Attachment
                .Body = "Kindly review the attached file for TMT-KSD upload." & vbNewLine & "Regards."
                .Attachments.add Range("L9").Value
                .send
            End With
            
            Set OutlookMail = Nothing
            Set OutlookApp = Nothing
        End If
    Next x
    
    Application.ScreenUpdating = True
    
End Sub
If highlighted in red, they are variables you are using in your code but haven't declared
If highlighted in blue, that is the part added to loop through rows
If highlight in green, an alternative way to concatenate a string
 
Last edited:
Upvote 0
Hello and welcome. I think you'll need to provide a little more detail before we can offer help:

[1] Is the attachment in Column C or Column L? You use "C9" in the message prompt but "L9" in the mail.
[2] Do you want to prompt the user to send a mail for every line?
[3] What is the first row of data? Is it row 9 as per your current code?
[4] Which column do you want to use to record "Email sent"?
[5] If the email has already been sent, do you want to ignore that row next time?

WBD
 
Upvote 0
wideboydixon said:
Hello and welcome. I think you'll need to provide a little more detail before we can offer help:

[1] Is the attachment in Column C or Column L? You use "C9" in the message prompt but "L9" in the mail.
[2] Do you want to prompt the user to send a mail for every line?
[3] What is the first row of data? Is it row 9 as per your current code?
[4] Which column do you want to use to record "Email sent"?
[5] If the email has already been sent, do you want to ignore that row next time?

WBD
Click to expand...


Hey,
[1] Attachment is in column L and the name of the file in column C
[2][4] I don't necessarily want to prompt every time....once they click on the command button to which the macro is assigned I definitely want column Q to record "Email Sent"
[3] first row of data is row 9
[5] yes I want that row to be ignored next time and the macro to run and send email for the new/next row.


your questions are really well constructed....made it easy for me to summaries everything I need.
Thanks plenty.
 
Upvote 0
Re: issues with VBA, please help!

JackDanIce said:
Welcome to the board. You have used variables you haven't declared. At the very top of your module, add line "Option Explicit" this will highlight variables missing.

Try:
Rich (BB code): 
Sub Email_Notification()

    Dim OutlookApp  As Object
    Dim OutlookMail As Object
    Dim ToName      As String
    Dim CCName      As String
    Dim cell        As Range
    Dim strMsg      As String
    
    Dim x           As Long
    Dim LR          As Long
    
    Application.ScreenUpdating = False
    
    LR = Cells(Rows.count, 14).End(xlUp).row
    For x = 9 To LR
    
        ToName = Range("N" & x).Value
        CCName = Range("P" & x).Value
        mattname = Range("C" & x).Value
            
        strMsg = "Do you want to send the following Email?@1@1Subject: " & SubName & _
            "@1@1To: " & ToName & "@1CC: " & CCName & _
            "@1With the following Attachments: @1" & mattname
        strMsg = Replace(strMsg, "@1", vbCrLf)
                    
        If MsgBox(strMsg, vbQuestion + vbYesNo, "Send Email") = vbYes Then
        
            Set OutlookApp = CreateObject("Outlook.application")
            Set OutlookMailitem = OutlookApp.CreateItem(0)
            
            With OutlookMailitem
                .Display
                .to = ToName
                .CC = CCName
                .Subject = "TMT-KSD File Review Request"
                .HTMLBody = Body & Attachment
                .Body = "Kindly review the attached file for TMT-KSD upload." & vbNewLine & "Regards."
                .Attachments.add Range("L9").Value
                .send
            End With
            
            Set OutlookMail = Nothing
            Set OutlookApp = Nothing
        End If
    Next x
    
    Application.ScreenUpdating = True
    
End Sub
If highlighted in red, they are variables you are using in your code but haven't declared
If highlighted in blue, that is the part added to loop through rows
If highlight in green, an alternative way to concatenate a string
Click to expand...


Works like a miracle....Thank you so very much
 
Upvote 0
Re: issues with VBA, please help!

You're welcome
 
Upvote 0
Re: issues with VBA, please help!

JackDanIce said:
You're welcome
Click to expand...


hey one more thing though.....when I close and open the file it starts from the first row again.....I want a 'yes' and 'no' value to update in column' q' automatically that when the macro is run and the email has been send...it should say 'yes' otherwise no, so that it move down the row and once I close and open again, it'd start from the column that contains value 'no' and not from the beginning.

thank for your help in advance.
seriously though, you could get paid for this stuff! :)
 
Upvote 0
Re: issues with VBA, please help!

Based on the code from @JackDanIce

Rich (BB code): 
Sub Email_Notification()

    Dim OutlookApp  As Object
    Dim OutlookMail As Object
    Dim ToName      As String
    Dim CCName      As String
    Dim cell        As Range
    Dim strMsg      As String
    
    Dim x           As Long
    Dim LR          As Long
    
    Application.ScreenUpdating = False
    
    LR = Cells(Rows.Count, 14).End(xlUp).Row
    For x = 9 To LR
    
        ToName = Range("N" & x).Value
        CCName = Range("P" & x).Value
        mattname = Range("C" & x).Value
        
        If Range("Q" & x).Value <> "Yes" Then
            strMsg = "Do you want to send the following Email?@1@1Subject: " & SubName & _
                "@1@1To: " & ToName & "@1CC: " & CCName & _
                "@1With the following Attachments: @1" & mattname
            strMsg = Replace(strMsg, "@1", vbCrLf)
                        
            If MsgBox(strMsg, vbQuestion + vbYesNo, "Send Email") = vbYes Then
            
                Set OutlookApp = CreateObject("Outlook.application")
                Set OutlookMailitem = OutlookApp.CreateItem(0)
                
                With OutlookMailitem
                    .Display
                    .to = ToName
                    .CC = CCName
                    .Subject = "TMT-KSD File Review Request"
                    .HTMLBody = Body & Attachment
                    .Body = "Kindly review the attached file for TMT-KSD upload." & vbNewLine & "Regards."
                    .Attachments.Add Range("L9").Value
                    .send
                End With
                
                Set OutlookMail = Nothing
                Set OutlookApp = Nothing
                
                Range("Q" & x).Value = "Yes"
            Else
                Range("Q" & x).Value = "No"
            End If
        End If
    Next x
    
    Application.ScreenUpdating = True
    
End Sub

WBD
 
Upvote 0
Re: issues with VBA, please help!

wideboydixon said:
Based on the code from @JackDanIce

Rich (BB code): 
Sub Email_Notification()

    Dim OutlookApp  As Object
    Dim OutlookMail As Object
    Dim ToName      As String
    Dim CCName      As String
    Dim cell        As Range
    Dim strMsg      As String
    
    Dim x           As Long
    Dim LR          As Long
    
    Application.ScreenUpdating = False
    
    LR = Cells(Rows.Count, 14).End(xlUp).Row
    For x = 9 To LR
    
        ToName = Range("N" & x).Value
        CCName = Range("P" & x).Value
        mattname = Range("C" & x).Value
        
        If Range("Q" & x).Value <> "Yes" Then
            strMsg = "Do you want to send the following Email?@1@1Subject: " & SubName & _
                "@1@1To: " & ToName & "@1CC: " & CCName & _
                "@1With the following Attachments: @1" & mattname
            strMsg = Replace(strMsg, "@1", vbCrLf)
                        
            If MsgBox(strMsg, vbQuestion + vbYesNo, "Send Email") = vbYes Then
            
                Set OutlookApp = CreateObject("Outlook.application")
                Set OutlookMailitem = OutlookApp.CreateItem(0)
                
                With OutlookMailitem
                    .Display
                    .to = ToName
                    .CC = CCName
                    .Subject = "TMT-KSD File Review Request"
                    .HTMLBody = Body & Attachment
                    .Body = "Kindly review the attached file for TMT-KSD upload." & vbNewLine & "Regards."
                    .Attachments.Add Range("L9").Value
                    .send
                End With
                
                Set OutlookMail = Nothing
                Set OutlookApp = Nothing
                
                Range("Q" & x).Value = "Yes"
            Else
                Range("Q" & x).Value = "No"
            End If
        End If
    Next x
    
    Application.ScreenUpdating = True
    
End Sub

WBD
Click to expand...






Thanks buddy, both of you @wideboydixon and @JackDanIce....
My files complete...owe you huge. thanks.
 
Upvote 0
Re: issues with VBA, please help!

You're welcome, glad it's resolved
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question
Excel Email Lock up
Replies
3
Views
207
Logit
Logit
J
  • Question
PDF to Email
Replies
6
Views
263
dermie_72
D
J
  • Question
VBA code to send an email if a certain IF Criteria is met that loops when the spreadsheet is open
Replies
1
Views
268
joeyc123
J
S
  • Solved
Fix Macro, open outlook instead of direct email send
Replies
1
Views
473
Sergio
S
The Ruff Report
  • Question
Help adjusting Macro to email multiple cc addresses
Replies
1
Views
409
The Ruff Report
The Ruff Report

Forum statistics

Threads
1,215,446
Messages
6,124,904
Members
449,194
Latest member
JayEggleton

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