# Use Excel VBA to create email with table in the middle



## HeroofTroy (May 2, 2019)

I have managed to get to grips with creating an email and adding the text i need including leaving in the default signature.
However what i need is to create 3 new macros with different text in an email but including a 4x2 table in the middle of the text. because of the number of variable options i think it would be better to have a blank table in email which i can add data to rather than copy from excel sheet.

the coding i have so far is

_Dim OutApp As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Recipient
Dim Recipients As Recipients
Dim HTMLbody As String_
_Set OutApp = CreateObject("Outlook.Application")
Set objOutlookMsg = OutApp.CreateItem(olMailItem)
With objOutlookMsg
.Display
End With
Signature = objOutlookMsg.HTMLbody_
_With objOutlookMsg
.Subject = "Non Compliant Purchase Order"
.HTMLbody = "<p>You recently created the following Purchase Order which is showing on our reports as being non-compliant as it has not been linked to a contract.<p>To enable us to correct this before the end of the reporting period please advise as soon as possible, but not later than <b>Wednesday DDth MONTH</b>, of the correct Contract Number.<p>A list of the current Contracts on AccountNI can be found here.<p>If you have any questions or require further guidance please feel free to give me a call." & vbNewLine & Signature

End With

Set OutApp = Nothing_

This is the output i would like

"You recently created the following Purchase Order which is showing on our reports as being non-compliant as it has not been linked to a contract.



PO Ref
PO Line Description
Supplier Name
PO Value
    

<tbody style="box-sizing: border-box;">

</tbody> 
To enable us to correct this before the end of the reporting period please advise as soon as possible, but *not later than Wednesday DDth Month*, of the correct Contract Number.

A list of the current Contracts on AccountNI can be found here

If you have any questions or require further guidance please feel free to give me a call."

I have checked a number of forums and cant get to grips with the data provided. I could create a dummy table somewhere in my excel document in order to copy into my email if that is easier but at the minute i cant identify how to edit my current macro to do this.

Grateful for any help you can provide


----------



## Norie (May 2, 2019)

You are using HTMLBody so why not create the table using HTML?

Where would the data for the table you want to display in the email come from?


----------



## HeroofTroy (May 2, 2019)

Norie said:


> You are using HTMLBody so why not create the table using HTML?
> 
> Where would the data for the table you want to display in the email come from?



I dont know how to do that. Everything i have managed so far has come from trial and error and using info found online.

The data will come from an excel spreadsheet but i am required to send multiple emails to different recipients with same template but single line of data which changes


----------



## Norie (May 3, 2019)

It should be quite straightforward to convert one line of data, with headings, to HTML.

In fact if you look here Convert Excel to HTML you'll find a function that does that.

There's also a simple example of how to use it when composing an email.


----------

