vba help - Outlook email on button click with Table

Mallesh23

Well-known Member
Joined
Feb 4, 2009
Messages
840
Office Version
  1. 2010
Platform
  1. Windows
Hi Team,

I want a one outlook Macro, which will draft new email with 4 columns in Body.
and to generate new email I want addin button in outlook only.

User will then fill in outlook table and send information manually, attached snapshot.



Thanks
mg
 

Attachments

  • Outlook Table - Snapshot.PNG
    Outlook Table - Snapshot.PNG
    26.4 KB · Views: 10

Some videos you may like

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

i1patrick

New Member
Joined
Oct 31, 2020
Messages
1
Office Version
  1. 2016
Platform
  1. Windows
Hello guys,

I'm very new as a registered user here, but I've been a fan of this forum for a while.

I have pretty much the same question as Mallesh23 above and would appreciate any answers.

Thanks,
Patrick
 

smozgur

BatCoder
Joined
Feb 28, 2002
Messages
1,280
Since this is an Excel forum, and the actual question can be actually answered in Outlook by using templates easily without using VBA (which should be asked in General Discussion & Other Applications forum), I will provide a VBA macro that will run in Excel to create an email message which contains a blank table.

It is done by using the .HTMLBody property of Outlook.MailItem object.

Make sure including Microsoft Outlook XX Object Library in VBE->Tools->References, or late binding can be also used with the CreateObject method.

VBA Code:
Sub newEmailWithTable()
Dim olApp As Outlook.Application
Dim olMessage As Outlook.MailItem
Dim strHtmlTable As String
Dim i As Integer
Dim j As Integer
Dim intCols As Integer
Dim intRows As Integer
Dim strSubject As String
Dim strColHeader As String
    
    ' Setting variables
    ' These variables can be passed as macro variables
    ' by setting the sub routine to include them
    ' this way the macro can be reused
    intCols = 4
    intRows = 10
    strSubject = "Subject goes here"
    strColHeader = "Test"

    ' Create Outlook session
    Set olApp = New Outlook.Application
    ' Create new Outlook mail item
    Set olMessage = olApp.CreateItem(olMailItem)
    
    With olMessage
      .Subject = strSubject
      ' Start building HTML code
      ' Style properties can be defined here
      ' Look at the existing CSS properties for table, th, and td elements
      strHtmlTable = "<html>" & _
                  "<head>" & _
                  "  <style>" & _
                  "    table {border: 1px solid #ccc;}" & _
                  "    td, th {border: 1px solid #ccc;width: 100px; height: 20px;padding: 0px}" & _
                  "  </style>" & _
                  "</head>" & _
                  "<body>" & _
                  "  <table cellspacing=""0"" cellpadding=""0"">" & _
                  "    <thead>" & _
                  "      <tr>"
      ' Set up column headers
      For j = 1 To intCols
        strHtmlTable = strHtmlTable & _
                    "        <th>" & strColHeader & j & "</th>"
      Next j
      ' End of table head, thead, element
      strHtmlTable = strHtmlTable & _
                  "      </tr>" & _
                  "    </thead>"
      ' Table rows
      For i = 1 To intRows
        strHtmlTable = strHtmlTable & "<tr>"
        ' Table columns
        For j = 1 To intCols
            strHtmlTable = strHtmlTable & "<td></td>"
        Next j
        strHtmlTable = strHtmlTable & "</tr>"
      Next i
      
      ' Finalize table
      strHtmlTable = strHtmlTable & _
                  "  </table>" & _
                  "</body>" & _
                  "</html>"
      ' Set the HTMLBody property of the mail item
      .HTMLBody = strHtmlTable
      ' Display the message window
      .Display
    End With
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,122,589
Messages
5,597,053
Members
414,118
Latest member
moversnpackers

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