Sending Outlook Emails from/using Excel VBA - Outlook and Excel

Dear Excel VBA experts,

I have a problem and hopefully you could help me out here. I have also attached an excel file for scenarios, as well as my desired outcome.

What I wanted on the final outcome is to have emails that I have created in Excel, to be sent out to the respective addresses (to, cc and/or bcc), along with the message and the signature.

In the attached example, there should be a total of 3 emails to be sent out (assuming that "to", "cc" and "bcc" are included as 1 email, as the content of the body is the same).

For example, in scenario 1, there should be 3 email addresses in the "to" and "cc" fields and 1 email address in the "bcc" field. With the message of the header (Excel Cell Range A1 to D1) in Excel as well as how it is shown in Excel of Cell Range A2 to D2, and finally the signature.

In scenario 2, 1 email address in the "to", "cc" and "bcc" field with header (Excel Cell Range A1 to D1) as well as Excel Cell Range A3 to D7, and the signature at the bottom.

In scenario 3, only 1 address in the "to" field, with header (Excel Cell Range A1 to D1) and Excel Cell Range A8 to D9, and the signature at the bottom.

So basically, column F is the "criteria" to check how many "lines" of data to input on the body before it decides to create a new email to send.

I hope that with my attachment and a little of explanation here, you understand completely what I desire to achieve.

Your help is deeply appreciated in making my work life easier!!!

I have written some code based on your posted workbook example - if the workbook is set up this way then the following code should work. It will calculate what data to attach to each email based on how your example workbook it setup. I have set up the code to Display the email - all you need to do is comment this out and uncomment the .send - if you have code set to send then the email will be sent automatically so I will leave this to you to decide - it is commented in the code as follows. I have attached an example workbook with the code so make sure it works to your specifications. I would recommend when you use this on real data that you leave the code to Display only just until the code has been tested to your satisfaction that the correct data is being placed into the email. You can change the various attributes such as the signature and leading comment in the body of the email - this again has been commented in the code so modify it to your needs. If you need any help then post back.
I have placed a button on the worksheet to run the code. There is error handling in the code so if there are any errors then please make sure you record the entire error when posting back or do a screen dump.

Option Explicit
    Const bodyString As String = "Please advised the following accounts."
    Const signStr As String = "John Doe" & vbCrLf & "ABC Company" & vbCrLf & "Tel:123-456-7890"
    'change the First string above to alter the leading message in the email
    'change the Second string above to change the signature in the email
    Dim dataHdrs As String
Sub writeEmails()
    Dim OutApp As Object
    Dim OutMail As Object
    
    Dim sndToList, ccToList, bccList, sList As String, cList As String, bList As String
    Dim subjectStr As String, k As Long, x As Long, bodyStr As String
    Dim dataBody
    On Error GoTo ErrHandler
    Application.ScreenUpdating = False
    Set OutApp = CreateObject("Outlook.Application")
    'determine data blocks
    ReDim dlMarkers(x)
    For k = 2 To Cells(Rows.Count, "A").End(xlUp).Row
    If Range("E" & k) <> "" Then
    ReDim Preserve dlMarkers(x)
    dlMarkers(x) = k
    x = x + 1
    End If
    Next
    ReDim Preserve dlMarkers(x)
    dlMarkers(x) = k
    x = 0
    dataHdrs = Range("A1") & Chr(9) & Chr(9) & Range("B1") & Chr(9) & Range("C1") & Chr(9) & Range("D1")
    For k = 2 To Cells(Rows.Count, "A").End(xlUp).Row
    sndToList = Application.Index(Application.Transpose(Range("F" & dlMarkers(x) & ":J" & dlMarkers(x))), 0, 1)
    ccToList = Application.Index(Application.Transpose(Range("K" & dlMarkers(x) & ":O" & dlMarkers(x))), 0, 1)
    bccList = Application.Index(Application.Transpose(Range("P" & dlMarkers(x) & ":T" & dlMarkers(x))), 0, 1)
    dataBody = Range("A" & dlMarkers(x)).Resize(dlMarkers(x + 1) - dlMarkers(x), 4)
    subjectStr = Range("E" & dlMarkers(x))
    sList = EmailList(sndToList)
    cList = EmailList(ccToList)
    bList = EmailList(bccList)
    bodyStr = BodyList(dataBody)
On Error Resume Next
            Set OutMail = OutApp.CreateItem(0)
            With OutMail
                .To = sList
                .cc = cList
                .bcc = bList
                .subject = subjectStr
                .Body = bodyStr
                '.Send ->Just uncomment this line and remove the Display command to send the email without displaying
                .Display ' this will make the email display
            End With
            On Error GoTo 0
x = x + 1
If UBound(dlMarkers) = x Then
Exit For
End If
Next
Set OutMail = Nothing
Set OutApp = Nothing
    Application.ScreenUpdating = True
    Exit Sub
ErrHandler:
MsgBox "Error Number" & Err.Number & "-" & Err.Description
Set OutMail = Nothing
Set OutApp = Nothing
End Sub

Function EmailList(addArr As Variant) As String
Dim t As Long
For t = LBound(addArr, 1) To UBound(addArr, 1)
    If Len(addArr(t, 1)) > 0 Then
    EmailList = EmailList & addArr(t, 1) & ";"
    Else
    Exit For
    End If
Next
If Len(EmailList) > 0 Then
    EmailList = Left(EmailList, Len(EmailList) - 1)
    Else
    EmailList = ""
    End If
End Function

Function BodyList(addArr As Variant) As String
Dim t As Long, dtStr As String
BodyList = bodyString & vbCrLf & vbCrLf & dataHdrs & vbCrLf
For t = LBound(addArr, 1) To UBound(addArr, 1)
If Len(addArr(t, 1)) > 0 Then
dtStr = dtStr & addArr(t, 1) & Chr(9) & Chr(9) & Chr(9) & Chr(9) & addArr(t, 2) & Chr(9) & Chr(9) & addArr(t, 3) & _
Chr(9) & Chr(9) & "$" & " " & Format(addArr(t, 4), "#,##0.00") & vbCrLf
Else
Exit For
End If
Next
BodyList = BodyList & dtStr & vbCrLf & vbCrLf & signStr
'Debug.Print BodyList
End Function

Fantastic - if you need any help just ask and thank you for the reps. Much appreciated

You would need to send the body as HTML and you can do this using html tags however you need to use the .htmlbody parameter and not .body. If you attach a word document with the signature exactly how you want I will write some html code to add for the signature. If you know html then have a look at http://www.excelforum.com/excel-prog...mail-body.html
If it is easier just attach a word document with the signature layout and I will write the html to add to the macro

Originally Posted by smuzoen

You would need to send the body as HTML and you can do this using html tags however you need to use the .htmlbody parameter and not .body. If you attach a word document with the signature exactly how you want I will write some html code to add for the signature. If you know html then have a look at http://www.excelforum.com/excel-prog...mail-body.html
If it is easier just attach a word document with the signature layout and I will write the html to add to the macro

Hi Smuzoen,

Was reading your post on Auto generating Emails. I need a Help on the same line.

Need a help from you, Please.
In the Macro should only pick those lines who's "Forcasted Deliveries Start Date" in column G is less than todays date and "Balance Qty" in Column I has some value (more than Zero).
I am referencing the attached file

Your help in this would be highly appreciated.

Thanks,
Chetan

Chetan,

Welcome to the Forum, unfortunately:

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.

Hello everyone,
New to this forum. I have been looking at the email sending coding. This is very nicely done. What I have been trying to do is similar and yet different. What I need is a macro that will respond to a yes or no in a column then copy a worksheet to another adding the fields to email. I'm not sure if I can used some of the code. Please let me know if this makes sense to you or not.
Please see new thread.