VBA Sending Email Based On Cell Values in Column

[richardtims]

Hello,

I am still somewhat new to the VBA coding, but I have learned an extreme amount in the past few months. The background of what I am needing to accomplish is to send reports based on whether I have updated the report. I have already figured out how to make the email get the subject, email address, path, and file name from the appropriate fields which has been challenging.

I am having 2 small issues however. First issue is I am trying to get my default Outlook email signature to be included in my emails that I am generating. I do need to I would prefer to use the .send over the .display if possible.

My second issue is setting up the macro to look at Column A to determine whether or not to send the email to that row of information. I want the macro to look at column A for as many lines are there (Currently 10 rows) and if the cell in column A is blank, I want it to send an email. If it has anything else like an "x", I want it to skip over that row and proceed to the next.

If this is covered in a different thread, please let me know. I have spent hours upon hours searching for a solution that I can implement without breaking what I have worked on so far.

I have pasted my code below. Please forgive me if there is unnecessary coding or missing coding. This is still somewhat new to me and any help is appreciated.

Sub MailReports()
Dim rng As Range
Dim OutApp As Object
Dim OutMail As Object
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)

Set ws = Sheets("Sheet1")
With OutMail
.To = ActiveSheet.Range("D4")
.CC = ""
.BCC = ""
.subject = Range("C4").Value
.Attachments.Add Range("E4").Value & "" & Range("F4").Value & Range("G4").Value
.Body = ""
.Display 'would prefer .Send instead of .Display

End With
Set OutMail = Nothing
Set OutApp = Nothing
End Sub

 

[jakeman]

Re: VBA Need Help Sending Email Based On Cell Values in Column

This code should work to resolve your first issue. Let me know if it works and if not, where it errors:

Public Sub emailblock()

Dim OApp As Object, OMail As Object, Signature As String, rng As Range


Set OApp = CreateObject("Outlook.Application")
Set OMail = OApp.CreateItem(0)
Set ws = Sheets("Sheet1")
    
    With OMail
    .display
    End With
        Signature = OMail.Body
    With OMail
    .To = ActiveSheet.Range("D4")
    .Subject = Range("C4").Value
    .Attachments.Add Range("E4").Value & "" & Range("F4").Value & Range("G4").Value
    .Body = "Add body text here" & vbNewLine & Signature
    .display 'would prefer .Send instead of .Display
    
    End With
    
Set OMail = Nothing
Set OApp = Nothing
End Sub

 

[richardtims]

Re: VBA Need Help Sending Email Based On Cell Values in Column

I tried your code and I am getting the signature on the email now, but it wants to bug at the attachment line. On 'Sheet1', I have the following values for the following cells E4="C:\Reports", F4="Quality Report", and "G4=".xlsx". Note that I do not have the "" in the actual cells, just the values within the quotation marks. In my original code, I had the "" that appears after the path by itself. I tried both adding it in the code line and also changing the path and it still didn't work. Also, I had the body text of the email in cell B4. Can I have a body set to a cell value and the signature as well? I hope I make sense.

 

[jakeman]

Re: VBA Need Help Sending Email Based On Cell Values in Column

Were you able to get the .Attachment code to work previously? Also, do the values in cells E4, F4, and G4 ever change or are they always constant? If constant, you might want to just use the entire string without separating it.

For example: ("C:\Reports Quality Report.xlsx")

I believe you do need to put the parentheses around the full path and file name.

As to your question about body text set to a cell, I would try this:

.Body = Worksheets("Sheet1").Range("B4")

 

[richardtims]

Re: VBA Need Help Sending Email Based On Cell Values in Column

Yes, my previous code at the top of this thread did attach the file perfectly. As far as testing the .Body line, the script doesn't make it that far. If I replace the .Body line and move it in from of the .Attachment line, it bugs at that line.

As I was replying, I did try your idea of making the file path one long path that included file name. It did work and the body did reference the cell contents properly, but I would still like to have the file name to be dynamic because some file names will change with the date. Also, when I did make that change to include the file name with the path, my signature became unformatted. Here is my code so far:

Public Sub emailblock()
Dim OApp As Object, OMail As Object, Signature As String, rng As Range

Set OApp = CreateObject("Outlook.Application")
Set OMail = OApp.CreateItem(0)
Set ws = Sheets("Sheet1")

With OMail
.Display
End With
Signature = OMail.Body
With OMail
.To = ActiveSheet.Range("D4")
.subject = Range("C4").Value
.Attachments.Add Range("E4").Value
.Body = Worksheets("Sheet1").Range("B4") & vbNewLine & Signature
.Display 'would prefer .Send instead of .Display

End With

Set OMail = Nothing
Set OApp = Nothing
End Sub

 

[jakeman]

Re: VBA Need Help Sending Email Based On Cell Values in Column

Unfortunately, I cannot fully test this code due to IT restrictions on my end. I'm wondering whether the Signature can removed at the .Body section. So change:

.Body = Worksheets("Sheet1").Range("B4") & vbNewLine & Signature

to

.Body = Worksheets("Sheet1").Range("B4") & vbNewLine

If you can get all of the above to work, then maybe we can figure out how to use a Dynamic file name.

 

[richardtims]

Re: VBA Need Help Sending Email Based On Cell Values in Column

So, I was playing around a bit and found that if I remove the .body line from the code and make some tweaks, it works. I would just have to insert a body if it becomes necessary. But everything else works, my .subject, my .attachment, and my .To line. Also, when I change the .Display to .Send, it sends it perfectly. Part 1 of my situation is SOLVED - Thanks LOTS!!!

I am pasting my code at the bottom of this reponse so that it may be of help to others.

Now, my second part of the question....I would like for this code to repeat itself looking for a value in column A and the respective row. If the value is null, I want it to send that email with that rows details. If the value is "x" or not null, I want it to skip over that row and test the next. Right now, I have 8 reports that go out at different times of the day, so row 4, 6, and 8 may need to go out in the morning, but not in the afternoon. So, I would clear the "x" on row 4, 6, and 8 and then run the macro. In turn, it would only send those three emails. Can you help? Here is my code so far......

Public Sub SendEmail()
Dim OApp As Object, OMail As Object, Signature As String, rng As Range
Set OApp = CreateObject("Outlook.Application")
Set OMail = OApp.CreateItem(0)
Set ws = Sheets("Sheet1")

With OMail
.Display
End With
With OMail
.To = ActiveSheet.Range("D4")
.subject = Range("C4").Value
.Attachments.Add Range("E4").Value
.Display 'Can substitute .Send to send instead of .Display which will allow edit before sending

End With

Set OMail = Nothing
Set OApp = Nothing
End Sub

 

[jakeman]

Re: VBA Need Help Sending Email Based On Cell Values in Column

So I'm not sure if I completely understand what information is on each row where the value in A is null, but here's what I think would work:

Sub SendEmail()
Dim N As Long, i As Long
Dim OApp As Object, OMail As Object, Signature As String, rng As Range

Set OApp = CreateObject("Outlook.Application")
Set OMail = OApp.CreateItem(0)
Set ws = Sheets("Sheet1")

N = Cells(Rows.Count, "A").End(xlUp).Row
For i = 2 To N
        If Cells(i, "A").Value = "" Then
            With OMail
                .Display
            End With
            With OMail
                .To = ActiveSheet.Range("D" & ActiveCell.Row)
                .Subject = Range("C" & ActiveCell.Row).Value
                .Attachments.Add Range("E" & ActiveCell.Row).Value
                .Display 'Can substitute .Send to send instead of .Display which will allow edit before sending
            End With

            Set OMail = Nothing
            Set OApp = Nothing

        End If
    Next i
End Sub

This will loop through each row in A and wherever a blank value is found, it should be able to read the contents of C,D, and E. Again, I can't fully test this but I believe it should get you closer to where you need to be.

 

[richardtims]

Re: VBA Need Help Sending Email Based On Cell Values in Column

I finally had a chance to try this and it is still not quite working. I have inserted a screenshot of the excel sheet I am using. As you can see, I always have a value in the Subject Column (Column C), so we can use this as a count column, but the count needs to start at row 4. In column A, I want to put an x on reports that I don't want to send. So, looking at the screenshot I have attached, I want to send row 5, but not row 4 or row 6. Also, I need to reserve the empty rows 1 and 2 for date headers and such. I hope this helps explain.

 

[jakeman]

Re: VBA Need Help Sending Email Based On Cell Values in Column

I'm not able to see the screenshot. I believe you can only upload the image from a URL, not from your desktop, etc. Are you able to see the image? I cannot.