Send Outlook email with table based on sum of cells in excel VBA

[tonyjyoo]

Hello,

I need assistance sending an outlook email from an excel file with <acronym title="visual basic for applications" style="border-width: 0px 0px 1px; border-top-style: initial; border-right-style: initial; border-bottom-style: dotted; border-left-style: initial; border-top-color: initial; border-right-color: initial; border-bottom-color: rgb(0, 0, 0); border-left-color: initial; border-image: initial; cursor: help; color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">VBA</acronym>.

Every email message will have the following content:


"Hi:

We are expecting the following NSCC Deposits.

	NSCC Deposits
Account Name:	Company A	Company B	Company C	Company D
Account Number:	412-170-6972	412-170-6956	412-170-6915	412-170-6931
Credit Amount:	$$	$$	$$	$$

<tbody>
</tbody>

Thanks,

Tony"

*** where each of the "Credit Amounts" in my table would be a SUM of 2 cells in a workbook I have - specifically, Company A's credit amount would be the sum of cells J26 and J27; Company B would be the sum of cells L26 andL27; etc. Is there a way I can create a macro within my excel workbook, have it look at the sum of these cells and send an email? The credit amounts in the body of my email are the only things changing; everything else is static.

Please let me know if you have any questions.

Thanks,
Tony

 

[tonyjyoo]

Hello,

I need assistance sending an outlook email from an excel file with <acronym title="visual basic for applications" style="border-width: 0px 0px 1px; border-top-style: initial; border-right-style: initial; border-bottom-style: dotted; border-left-style: initial; border-top-color: initial; border-right-color: initial; border-bottom-color: rgb(0, 0, 0); border-left-color: initial; border-image: initial; cursor: help; color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">VBA</acronym>.

Every email message will have the following content:


"Hi:

We are expecting the following NSCC Deposits.

	NSCC Deposits
Account Name:	Company A	Company B	Company C	Company D
Account Number:	412-170-6972	412-170-6956	412-170-6915	412-170-6931
Credit Amount:	$$	$$	$$	$$

<tbody>
</tbody>

Thanks,

Tony"

*** where each of the "Credit Amounts" in my table would be a SUM of 2 cells in a workbook I have - specifically, Company A's credit amount would be the sum of cells J26 and J27; Company B would be the sum of cells L26 andL27; etc. Is there a way I can create a macro within my excel workbook, have it look at the sum of these cells and send an email? The credit amounts in the body of my email are the only things changing; everything else is static.

Please let me know if you have any questions.

Thanks,
Tony

bump

 

[Trevor_S]

It is possible, I've done it in a spreadsheet at a former employer! Unfortunately I haven't got outlook on a computer at home, so can't write and test the exact code you need, but hopefully this will help...

Use HTML... There's an article on msdn that gives some VBA code: https://msdn.microsoft.com/en-us/library/office/aa171418(v=office.11).aspx
The code in this article shows you how to set the email to be HTML format. Then you need to write the HTML to be in the message body. Don't know how much HTML you know, but as an example you can do a basic 2x2 table containing the numbers 1-4 like this:

<table>
<tr>
<td>1</td>
<td>2</td>
</tr>
<tr>
<td>3</td>
<td>4</td>
</tr>
</table>

I've used line breaks to try to show the structure, but the html code can just flow as one long string on a single line. There's all sorts of formatting commands that can be added in to make it look more professional. If you want to test your basic HTML layout, type it into notepad, save it with a .HTML file extension instead of .txt , and open it in a browser. That way you can easily amend it as necessary, without having to keep running the macro.

The HTML code is treated as a string expression in the .HTMLBody = line of the VBA code. As you want a value from the spreadsheet, you need to create a string being two (or more) substrings concatenated around some code to get that value. So using my HTML example above, if you wanted to replace the "1" in the top left box with the value of cell A1, the VBA code line would be:

[HTML]
.HTMLBody = "<table><tr><td>" & Range("A1").Value & "</td><td>2</td></tr><tr><td>3</td><td>4</td></tr></table>"
[/html]

You can obviously concatenate as many substrings as needed to insert all the values you need.

Hope this helps!

 

[tonyjyoo]

It is possible, I've done it in a spreadsheet at a former employer! Unfortunately I haven't got outlook on a computer at home, so can't write and test the exact code you need, but hopefully this will help...

Use HTML... There's an article on msdn that gives some VBA code: https://msdn.microsoft.com/en-us/library/office/aa171418(v=office.11).aspx
The code in this article shows you how to set the email to be HTML format. Then you need to write the HTML to be in the message body. Don't know how much HTML you know, but as an example you can do a basic 2x2 table containing the numbers 1-4 like this:

1234

I've used line breaks to try to show the structure, but the html code can just flow as one long string on a single line. There's all sorts of formatting commands that can be added in to make it look more professional. If you want to test your basic HTML layout, type it into notepad, save it with a .HTML file extension instead of .txt , and open it in a browser. That way you can easily amend it as necessary, without having to keep running the macro.

The HTML code is treated as a string expression in the .HTMLBody = line of the VBA code. As you want a value from the spreadsheet, you need to create a string being two (or more) substrings concatenated around some code to get that value. So using my HTML example above, if you wanted to replace the "1" in the top left box with the value of cell A1, the VBA code line would be:

[HTML]
.HTMLBody = "" & Range("A1").Value & "234"
[/HTML]

You can obviously concatenate as many substrings as needed to insert all the values you need.

Hope this helps!

Hi Trevor,

I'm so sorry but I am unfamiliar with HTML. As such, I am lost as to how I can incorporate this into VBA... could you write out the VBA in more detail according to my initial prompt? Thanks again for your help.

 

[Trevor_S]

Here's my best attempt, as I said, I can't test it as I no longer have Outlook.
Although it will say HTML code below, it is in fact VBA, I've just had to tag it as HTML so that the control characters aren't converted.
This should give you what you want as a basic table.
If your first two ranges to total are J26:J27 and L26:L27, I'm assuming that the other two are N26:N27 and P26:P27 (i.e. you have spare columns between the ones you need)

[html]
Sub CreateHTMLMail()'Creates a new e-mail item and modifies its properties.

    Dim olApp As Outlook.Application
    Dim objMail As Outlook.MailItem
    Set olApp = Outlook.Application
    'Create e-mail item
    Set objMail = olApp.CreateItem(olMailItem)

    With objMail
       'Set body format to HTML
       .BodyFormat = olFormatHTML
       .HTMLBody = "<table><tr><td>NSCC Deposits</td><td></td><td></td><td></td><td></td></tr><tr><td>Account Name:</td><td>Company A</td><td>Company B</td><td>Company C</td><td>Company D</td></tr><tr><td>Account Number:</td><td>412-170-6972</td><td>412-170-6956</td><td>412-170-6915</td><td>412-170-6931</td></tr><tr><td>Credit Amount:</td><td>" & Range("J26").Value + Range("J27").Value & "</td><td>" & Range("L26").Value + Range("L27").Value & "</td><td>" & Range("N26").Value + Range("N27").Value & "</td><td>" & Range("P26").Value + Range("P27").Value & "</td></tr></table>"       .Display
    End With
End Sub
[/html]

 

[Trevor_S]

I've just noticed that at the end of the longest line of code it says .Display
There should be a line break before .Display so that it appears on its own line.

 

[tonyjyoo]

Here's my best attempt, as I said, I can't test it as I no longer have Outlook.
Although it will say HTML code below, it is in fact VBA, I've just had to tag it as HTML so that the control characters aren't converted.
This should give you what you want as a basic table.
If your first two ranges to total are J26:J27 and L26:L27, I'm assuming that the other two are N26:N27 and P26:P27 (i.e. you have spare columns between the ones you need)

[html]
Sub CreateHTMLMail()'Creates a new e-mail item and modifies its properties.

    Dim olApp As Outlook.Application
    Dim objMail As Outlook.MailItem
    Set olApp = Outlook.Application
    'Create e-mail item
    Set objMail = olApp.CreateItem(olMailItem)

    With objMail
       'Set body format to HTML
       .BodyFormat = olFormatHTML
       .HTMLBody = "NSCC DepositsAccount Name:Company ACompany BCompany CCompany DAccount Number:412-170-6972412-170-6956412-170-6915412-170-6931Credit Amount:" & Range("J26").Value + Range("J27").Value & "" & Range("L26").Value + Range("L27").Value & "" & Range("N26").Value + Range("N27").Value & "" & Range("P26").Value + Range("P27").Value & ""       .Display
    End With
End Sub
[/html]

It did not work.

If I try using a HTML Table generator online and insert in vba, it does not work either.

 

[tonyjyoo]

Ok I adjusted it and it DOES pull in the table values, but it does not have borders and it seems to be skipping my first steps:

Sub Mail_small_Text_Outlook()


    Dim OutApp As Object
    Dim OutMail As Object
    Dim strbody As String


    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)


    strbody = "Hi:" & vbNewLine & vbNewLine & _
              "We are expecting the following NSCC deposits."
              
    On Error Resume Next
    With OutMail
        .To = "anthony.yoo@jackson.com"
        .CC = ""
        .BCC = ""
        .Subject = "This is the Subject line"
        .BodyFormat = olFormatHTML
        .HTMLBody = "<table><tr><td>NSCC Deposits</td><td></td><td></td><td></td><td></td></tr><tr><td>Account Name:</td><td>Company A</td><td>Company B</td><td>Company C</td><td>Company D</td></tr><tr><td>Account Number:</td><td>412-170-6972</td><td>412-170-6956</td><td>412-170-6915</td><td>412-170-6931</td></tr><tr><td>Credit Amount:</td><td>" & Range("J26").Value + Range("J27").Value & "</td><td>" & Range("L26").Value + Range("L27").Value & "</td><td>" & Range("N26").Value + Range("N27").Value & "</td><td>" & Range("P26").Value + Range("P27").Value & "</td></tr></table>"
        .Display
        .Send
    End With
    On Error GoTo 0


    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub

How can I get the "Hi: We are expecting the foillowing NSCC deposits" to show as well as some borders for the table?

 

[Trevor_S]

In my original code, replace:

<table>

With:

Hi: We are expecting the following NSCC deposits<p><table border=" & chr(34) & "1" & chr(34) & ">

To explain what this is doing ... You're just starting the string with your introductory text, the p marker tells the HTML code to start the table as a new paragraph, rather than tagging it on wherever the text ends. You can put a closing sentence/signature after the table by replacing:

</table>

With:

</table><p>Thanks,<p>Tony

The html command to show borders in the table is borders="1". The trouble with this is that VBA would interpret the quotes around the 1 as ending/starting strings. So you need to make the quotes by concatenating in the chr() number for quotes, which I believe is 34.

The following website gives some good examples of HTML commands, which may help develop your email: HTML Reference

 

[tonyjyoo]

In my original code, replace:

With:

Hi: We are expecting the following NSCC deposits

To explain what this is doing ... You're just starting the string with your introductory text, the p marker tells the HTML code to start the table as a new paragraph, rather than tagging it on wherever the text ends. You can put a closing sentence/signature after the table by replacing:

With:

Thanks,
Tony

The html command to show borders in the table is borders="1". The trouble with this is that VBA would interpret the quotes around the 1 as ending/starting strings. So you need to make the quotes by concatenating in the chr() number for quotes, which I believe is 34.

The following website gives some good examples of HTML commands, which may help develop your email: HTML Reference

Makes sense. However, it is only giving me an outside border. How can I set it up to create borders like in my post #1? Also, how can I change the font?