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

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
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!

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

S
  • Question Question
VBA sending an email of two conditions are met
Replies
1
Views
31
mumps
mumps
C
  • Question Question
Send Email from Excel - Launch User's Default Email Client
Replies
3
Views
276
Micron
M
D
  • Question Question
VBA change to not show a sent email in outlook
Replies
6
Views
417
Dspace
D
C
  • Solved
Add a table to e-mail to be sent to multiple addreses
Replies
3
Views
126
mumps
mumps
KlausW
  • Solved
Change Attachments from XLSM to XLSX in mail
Replies
5
Views
275
Micron
M

Forum statistics

Threads
1,223,221
Messages
6,170,819
Members
452,354
Latest member
yuzha

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