macro to send email (outlook) from excel with table

tonyjyoo

Board Regular
Joined
Aug 5, 2016
Messages
167
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 ACompany BCompany CCompany D
Account Number:412-170-6972412-170-6956412-170-6915412-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.

I am somewhat aware I will need to use HTMLbody for my table. I just don't know how to write the VBA for creating a table like above within the body of my outlook email.
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

tonyjyoo

Board Regular
Joined
Aug 5, 2016
Messages
167
How can I adjust the following code to add a table within the body of my email? What vba line items can I add:

Code:
Sub Mail_small_Text_Outlook()
[COLOR=black]'For Tips see: http://www.rondebruin.nl/win/winmail/Outlook/tips.htm
'Working in Office 2000-2016[/COLOR]
    Dim OutApp As Object
    Dim OutMail As Object
    Dim strbody As String

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

    strbody = "Hi there" & vbNewLine & vbNewLine & _
              "This is line 1" & vbNewLine & _
              "This is line 2" & vbNewLine & _
              "This is line 3" & vbNewLine & _
              "This is line 4"

    On Error Resume Next
    With OutMail
        .To = "ron@debruin.nl"
        .CC = ""
        .BCC = ""
        .Subject = "This is the Subject line"
        .Body = strbody
        [COLOR=black]'You can add a file like this
        '.Attachments.Add ("C:\test.txt")[/COLOR]
        .Send  [COLOR=black] 'or use .Display[/COLOR]
    End With
    On Error GoTo 0

    Set OutMail = Nothing
    Set OutApp = Nothing [COLOR=#3366CC]End Sub
[/COLOR]

In order to have a table like the one I posted in post #1.
 
Upvote 0

Forum statistics

Threads
1,191,191
Messages
5,985,209
Members
439,947
Latest member
fabiannic

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
Top