Solved How to edit an excel email macro to send only 1 email?

Microsoft Excel 2010 - complete product...
January 22, 2018 at 01:40:26
Specs: Windows 7, 2,4 GHz / 4 GB
Hello,

I got a macro online for sending emails given a condition. It works great if you have 1-2 entries that
require email sending based on the condition set. But when it sends up to 10 mails daily to the same
person it becomes kind of annoying.

I will post the macro I use below, but first I want to say what I would like to do and don't know exactly
how (I am a beginner at VBA language):

--> I want to modify the macro so that for multiple entries as per the condition, it sends only 1 email
with all the entries specified in body.

The columns are:
A - name of the person to send email to
B+C - email and CC email
D - condition, if yes send email, if no don't
E - company name
F - current no.
G - sector to be audited
H/I - date to begin / end audit
J/K - days left until beginning / end of the audit
L - audit done: if yes, column D becomes no and green

And here is the macro I use:

Sub audit()
    Dim OutApp As Object
    Dim OutMail As Object
    Dim cell As Range
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
    Set OutApp = CreateObject("Outlook.Application")

    On Error GoTo cleanup
    For Each cell In Columns("B").Cells.SpecialCells(xlCellTypeConstants)
        If cell.Value Like "?*@?*.?*" And _
           LCase(Cells(cell.Row, "D").Value) = "yes" Then
            Set OutMail = OutApp.CreateItem(0)
            On Error Resume Next
            With OutMail
                .To = cell.Value
                .CC = Cells(cell.Row, "C").Value
                .BCC = ""
                .Subject = "In " & Cells(cell.Row, "J").Value & " DAYS you must start AUDIT at " & Cells(cell.Row, "E").Value & " for sector " & Cells(cell.Row, "G").Value

                .Body = "Attention, " & Cells(cell.Row, "A").Value & "!" & _
                        vbNewLine & vbNewLine & _
                        "AUDIT at " & Cells(cell.Row, "G").Value & " from " & Cells(cell.Row, "E").Value & " must take place between " & Cells(cell.Row, "H").Value & " and " & Cells(cell.Row, "I").Value & _
                        vbNewLine & vbNewLine & _
                        "Days left until the start of the audit period: " & Cells(cell.Row, "J").Value & " DAYS!" & _
                        vbNewLine & vbNewLine & _
                        "You will keep receiving this email every 2 days." & _
                        vbNewLine & vbNewLine & _
                        "If you've done the audit already, go into the excel file and write the word yes in column K. In column D the color must become green automatically. Unless you do this, you will keep receiving this email every 2 days."
                .Send
                '.Display
            End With
            On Error GoTo 0
            Set OutMail = Nothing
        End If
    Next cell

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


See More: How to edit an excel email macro to send only 1 email?

Reply ↓  Report •

✔ Best Answer
January 26, 2018 at 12:17:26
I think this may be closer to what you are looking for.

One issue that I see is that the original code seemed to create a custom Subject line for each email based on the values in Columns J, G & E. Now that you want to include all audit items in one email, I'm not sure
what you want the Subject line to say. I put Generic Subject Line in the code as a space holder.

Let me know hat you think...

Sub audit()
    Dim OutApp As Object
    Dim OutMail As Object
    Dim cell As Range
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
    Set OutApp = CreateObject("Outlook.Application")

    On Error GoTo cleanup
    
    
'Store To and CC email Addresses, Build temporary Body string based on Yes in Column D
    For Each cell In Columns("B").Cells.SpecialCells(xlCellTypeConstants)
        If cell.Value Like "?*@?*.?*" And _
           LCase(Cells(cell.Row, "D").Value) = "yes" Then
            toAddy = cell.Value
            ccAddy = Cells(cell.Row, "C").Value
            tmpBody = tmpBody & Cells(cell.Row, "E").Value & _
                      " Days left until the start of the audit period: " & _
                        Cells(cell.Row, "J").Value & " DAYS!" & _
                        vbNewLine & vbNewLine
           End If
    Next cell
         
'Complete Body string
         tmpBody = "Attention!" & vbNewLine & vbNewLine & _
                         tmpBody & _
                        vbNewLine & vbNewLine & _
                        "You will keep receiving this email every 2 days." & _
                        vbNewLine & vbNewLine & _
                        "If you've done the audit already, go into the excel file and " & _
                        "write the word yes in column K." & " In column D the color must " & _
                        "become green automatically. Unless you do this, you will keep " & _
                        "receiving this email every 2 days."

'Send email
          Set OutMail = OutApp.CreateItem(0)
            On Error Resume Next
            With OutMail
                .To = toAddy
                .CC = ccAddy
                .BCC = ""
                .Subject = "Generic Subject Line" ' "In " & Cells(cell.Row, "J").Value & _
                                                     " DAYS you must start AUDIT at " & _
                                                     Cells(cell.Row, "E").Value & " for sector " & _
                                                     Cells(cell.Row, "G").Value
                .Body = tmpBody
                '.Send
                .Display
            End With
            On Error GoTo 0
            Set OutMail = Nothing
        
cleanup:
    Set OutApp = Nothing
    Application.ScreenUpdating = True
End Sub

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code



#1
January 22, 2018 at 19:38:22
Before I can help you, I need to understand the issue better.

It appears that the code is checking for an email address in Column B and then the string "Yes" in column D. If D contain Yes, the code sends an email the person whose email address is that row.

How is that the same person is getting "up to 10 mails daily"?

Is their email address listed multiple times in Column B? If so, are the same email addresses grouped together in contiguous rows or are they spread out all over the place?

If they are spread out, can the worksheet be sorted so that they are grouped together?

The more detail that you can provide as to the layout of your spreadsheet and the process involved, the better the chance that we can help.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

#2
January 24, 2018 at 23:01:40
Thanks for the response.

The email address is the same spread over 50 rows in column B, one after the other, no free cells. In principle, these addresses in column B should never change as it is the same auditor and if they change, they all do.
There are 10 yes in column D, so whenever the macro checks it'll send an email for each yes it finds.

I'm using Scheduled Tasks to open a bat file opening the excel file through a vbscript file to run the macro once every 2 days. Condition set in column D is 10 days before the date the audit starts.

I would like to change it in a way that the auditor gets 1 email with all info in the rows. I can add the email to 1 cell or to the macro itself, but I don't know how to make a loop in vba to check all the rows and add all the information to 1 email body, like:

Attention AUDITOR NAME!
In the following days you will have N audits:
- audit for sector 1 from company X starts on 24.01.2018, in A days from now
- audit for sector 2 from company X starts on 25.01.2018, in B days from now
- audit for sector 1 from company Y starts on 29.01.2018, in C days from now
- audit for sector 2 from company Y starts on 31.01.2018, in D days from now
and so on

and then the last 2 lines of plain text written in the macro above.

I hope the information is detailed enough, but I can answer any questions, no secrets.

Thank you in advance.

message edited by Mrrrr


Reply ↓  Report •

#3
January 26, 2018 at 04:16:38
You could take the building and sending of the email out of the For Each loop and move it to the bottom of macro so that the email only gets sent once.

As far as I can tell, the only task that the loop needs to do is build the ".Body" string.

You could do that with something like the following. (Untested as I don't have access to
VBA right now)

Inside the loop you place code similar to this:

tmpBody = tmpBody & (your "Audit at" code above) & vbCrLf

This should build a multi-line string with the audit info when each Yes is found.

After the loop is complete, add (&) the "Attention" opening and the closing sentences to the tmpBody variable.

Finally, in the email section, use

.Body = tmpBody

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

Related Solutions

#4
January 26, 2018 at 05:46:25
Thanks for the response.

I must be doing something wrong though, as the macro is not displaying anything anymore.

So this is how it looks now:

     Dim tmpBody As String 'added this, won't work with or without it

Now the loop and mail creation:

    For Each cell In Columns("B").Cells.SpecialCells(xlCellTypeConstants)
        If cell.Value Like "?*@?*.?*" And _
           LCase(Cells(cell.Row, "D").Value) = "yes" Then
           tmpBody = tmpBody & Cells(cell.Row, "E").Value & vbCrLf
        End If
    Next cell
           tmpBody = tmpBody & "Attention ....etc. "

  Set OutMail = OutApp.CreateItem(0)
            On Error Resume Next
            With OutMail
                .To = cell.Value
                .CC = Cells(cell.Row, "C").Value
                .BCC = ""
                .Subject = "In " & Cells(cell.Row, "J").Value & " DAYS you must begin audit at " & Cells(cell.Row, "E").Value & " - sector " & Cells(cell.Row, "G").Value
                .Body = tmpBody
                '.Send
                .Display
            End With

I actually don't know how to write the code; I just understand what it does here or there and the logic
of things. But there's a gap between that and actually writing the code myself. I just got everything
online and added my cell values and body content, but moving things out of the loop and into the loop -
never did. So please bear with me through the process.

So... what is wrong?

Thank you.


Reply ↓  Report •

#5
January 26, 2018 at 06:42:23
Edit: Read this post, but then see my following post. I have discovered an issue with incorporating your snippet into the overall macro.


First, let's start with this:

The debugging techniques found in the following tutorial can be very helpful in not only finding problems in your own code, but (and possibly more importantly) can be used to reverse engineer code that you didn't write as a means to understand what it is doing.

I learned these debugging steps piecemeal and eventually decided to put together a short tutorial.

https://www.computing.net/howtos/sh...

That said, I do not see anything wrong with your code. I tested the snippet that you included and it worked fine after the addition of this line:

Set OutApp = CreateObject("Outlook.Application")

I started with this:

       A               B            C        D          E 
1                fake@email.com			
2                fake@email.com             Yes         1
3                fake@email.com                         2
4                fake@email.com             Yes         3
5                fake@email.com                         4
6                fake@email.com             Yes         5

I ran your snippet and my email shows this:

1 
3 
5 
Attention ....etc. 


When you say "the macro is not displaying anything anymore" do you mean that the code runs without any errors but doesn't produce the email or that the email is empty or something else. Please clarify what "not displaying anything" means.

BTW...the first thing I did was to set a Watch on tmpBody to ensure that the string was being built correctly as I single stepped (F8) through the code. I could see the string being built each time Yes was found (minus the line feeds since the Watch window doesn't display them) The tutorial will explain the Watch and Single Step process.

You could also insert a MsgBox instruction in the loop to watch the string being built.

MsgBox tmpBody

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

message edited by DerbyDad03


Reply ↓  Report •

#6
January 26, 2018 at 09:58:10
I believe that the reason your email is blank is because of the range variable cell that you are using.

In your original code, the value of the cell.Row and cell.Value was known because of the "For Each cell" loop. cell as being used within the loop so VBA knew what to do with it. Once the loop has completed, the range variable cell has no meaning, therefore there is no cell.value or cell.Row.

When the code sees that variable, it throws up an error. The On Error Resume Next instruction then moves the code to the next line, another error, another next line, etc. The code continues until is is done, but because of the errors, everything that required something from the range variable cell is empty.

I was able to get your test snippet to work because (at least as far as the Body was concerned) your snippet didn't reference cell to build tmpBody. Now I see that the To:, Subject:, etc. are all blank because of the same (hidden) errors.

I'll see if I can come up with a workaround.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

#7
January 26, 2018 at 12:17:26
✔ Best Answer
I think this may be closer to what you are looking for.

One issue that I see is that the original code seemed to create a custom Subject line for each email based on the values in Columns J, G & E. Now that you want to include all audit items in one email, I'm not sure
what you want the Subject line to say. I put Generic Subject Line in the code as a space holder.

Let me know hat you think...

Sub audit()
    Dim OutApp As Object
    Dim OutMail As Object
    Dim cell As Range
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
    Set OutApp = CreateObject("Outlook.Application")

    On Error GoTo cleanup
    
    
'Store To and CC email Addresses, Build temporary Body string based on Yes in Column D
    For Each cell In Columns("B").Cells.SpecialCells(xlCellTypeConstants)
        If cell.Value Like "?*@?*.?*" And _
           LCase(Cells(cell.Row, "D").Value) = "yes" Then
            toAddy = cell.Value
            ccAddy = Cells(cell.Row, "C").Value
            tmpBody = tmpBody & Cells(cell.Row, "E").Value & _
                      " Days left until the start of the audit period: " & _
                        Cells(cell.Row, "J").Value & " DAYS!" & _
                        vbNewLine & vbNewLine
           End If
    Next cell
         
'Complete Body string
         tmpBody = "Attention!" & vbNewLine & vbNewLine & _
                         tmpBody & _
                        vbNewLine & vbNewLine & _
                        "You will keep receiving this email every 2 days." & _
                        vbNewLine & vbNewLine & _
                        "If you've done the audit already, go into the excel file and " & _
                        "write the word yes in column K." & " In column D the color must " & _
                        "become green automatically. Unless you do this, you will keep " & _
                        "receiving this email every 2 days."

'Send email
          Set OutMail = OutApp.CreateItem(0)
            On Error Resume Next
            With OutMail
                .To = toAddy
                .CC = ccAddy
                .BCC = ""
                .Subject = "Generic Subject Line" ' "In " & Cells(cell.Row, "J").Value & _
                                                     " DAYS you must start AUDIT at " & _
                                                     Cells(cell.Row, "E").Value & " for sector " & _
                                                     Cells(cell.Row, "G").Value
                .Body = tmpBody
                '.Send
                .Display
            End With
            On Error GoTo 0
            Set OutMail = Nothing
        
cleanup:
    Set OutApp = Nothing
    Application.ScreenUpdating = True
End Sub

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

#8
January 28, 2018 at 22:49:30
Hey,

Sorry for the late response. I tested the code you put in last post and it worked great. Adapted it and it worked until it didn't show email anymore. Then re-read what you said in the post before last and noticed I can't add cell values to body string. No problem, at least now I know why your first suggestion (post #3) wasn't working - because I had added cell.Value to tmpBody. Now I could do a HTMLBody to make the email look better, but I will see how that works.

Regarding this matter, everything works fine and it's exactly what I wanted.
Thank you very much for solving this for me!

Just wanted to add that I managed to make it look better with HTML, without running into trouble. Thank you again!

message edited by Mrrrr


Reply ↓  Report •

#9
January 29, 2018 at 04:04:58
I'm glad you got it working. Come on back if you have any other issues.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

Ask Question