• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Unable to add the table in the body of the outlook mail.

Jagdev Singh

Active Member
Hi Expert.

I have a macro in excel which send the email to the receipent from it. Everything works fine but the issue is with the body of the mail. I am not able to add the table in the body section in the excel. When I try to add the table the excel consider it is a text and replicate the same in the body of the outlook mail.

Could you please help me to fix this issue. Please refer the E4 (Body) section of the macro.

Please find the attached macro with the mail.

Regards,
Jaggi
 

Attachments

  • Email macro.xlsm
    20.9 KB · Views: 6
Hi Dan,

Could you please let me know how did you add the table in the cell. I am still not able to do it.

What I am doing, I am creating the body in the word doc with table in it and try to insert the contain in the Excel under E4. When I try to run the code it convert the entire data into text in outlook. Please help me with the process you used to add the table.

Regards,
Jagdev
 
Hi Experts

I want the below table to be added in the E4 cell of the above attached macro with the content of the cell

Dear Sir ,

We would like to wish you a Merry chirtmas & Happy New Year.

Ref Ccy Amount Name DueDate
133456F12 EUR 1,084.00 ABC Company 15/10/2014



Regards,

Jagdev


I am not able to add the table above, but I am trying to give you the idea what I am looking for.

Regards,
Jaggi
 
Hi Dan

In E4 cell we are adding the body of the mail. Which we will see in the body section of the outlook.

I am fine when we use Plain text in the body, but facing issue If I need to add something like table infor in it.

Example -
Dear Sir ,

We would like to wish you a Merry chirtmas & Happy New Year.


Ref Ccy Amount Name DueDate


133456F12 EUR 1,084.00 ABC Company 15/10/2014

Regards,

Jagdev
The bold section should be in tabular form in the outlook mail body..

Hope I clear your doubt.

Regards,
Jaggi
 
ehhhh....


Seems like you should be able to do it either in the half ass field & char10 & field & char10 (easy) or I guess you should try to manipulate the whole text thing in html?

That sounds like a bitch....
 
Hi Dan

I tried many combination, but unable to add table field in the body. This is the only thing I am stuck with as of now. Please let me know if there is a way around for it.

Regards
Jaggi
 
Hi Experts

I anyhow manage to add the table field in the body of the outlook via excel. I was wondering to know that is it possible to refresh the table in Sheet2 with the updated information available in the Raw_Data sheet in the attached Macro.

Let me make it bit clear:

There are 3 sheets in the attached macro.

First sheet - It is the sheet from which the email gets generated.
Second Sheet - It is the sheet from which the table will be pull to the outlook mail body.
Third sheet - It is a sheet in which the RAW data will be available to add in the table available in sheet2.

The RAW_Data heading is same as that of Sheet2 table heading. Is it possible to set the loop on sheet2. I mean the sheet 2 (table data) gets updated from RAW_Data Sheet everytime the mails are triggered from Email_Sheet.

Currently I have to manually update the Sheet2 table data with the RAW data sheet in case if there is more than 1 email to sent.

It will really helpful if this is automated.

We will add the data in the RAW_Data which we need to send at a moment of time.

Thanks in advance for your support.

Regards,
Jaggi
 

Attachments

  • Email macro.xlsm
    35.5 KB · Views: 6
Hi All

With the help of below code. I am able to solve the above issue. I am pasting it here and hope that it solve someone purpose with same query. You need to tweak the above macro little bit to meet the spec.

Option Explicit
Sub Preview()
SendEmail False
lbl_Exit:
Exit Sub
End Sub
Sub NoPreview()
SendEmail True
lbl_Exit:
Exit Sub
End Sub
Sub SendEmail(Optional bNoPreview As Boolean)
Dim iRec As Long
Dim OutApp As Object
Dim OutMail As Object
Dim olInsp As Object
Dim wdDoc As Object
Dim wdRng As Object
Dim rng As Range
Dim StrBody As String
Dim StrBody1 As String
Dim i As Long
Dim Subj As String
Dim FilePath As String
Dim EmailTo As String
Dim CCto As String
With Range("MergeData")
For i = 1 To .Rows.Count
Range("MergeRecord") = i - 1
Set rng = Nothing
Subj = .Cells(i, "A").Value
FilePath = .Cells(i, "B").Value
EmailTo = .Cells(i, "C").Value
CCto = .Cells(i, "D").Value
MsgBox Subj
Application.DisplayAlerts = False
Set rng = Sheets("Sheet2").Range("A1:E2").SpecialCells(xlCellTypeVisible)
rng.Copy
If rng Is Nothing Then
MsgBox "The selection is not a range or the sheet is protected" & _
vbNewLine & "please correct and try again.", vbOKOnly
Exit Sub
End If
With Application
.EnableEvents = False
.ScreenUpdating = False
End With
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
StrBody = "Dear Sir," & vbCr & vbCr & _
"Please be advised that we have given entry outstanding in our books." & vbCr & vbCr
StrBody1 = vbCr & vbCr & "We have attached copy document for your reference. Please could you have a look and provide your agreement and settlement date." & vbCr & vbCr & _
"Regards," & vbCr & vbCr
On Error Resume Next
With OutMail
.To = EmailTo
.CC = CCto
.BCC = ""
.Subject = Subj
.BodyFormat = 2
Set olInsp = .GetInspector
Set wdDoc = olInsp.WordEditor
Set wdRng = wdDoc.Range(0, 0)
wdRng.Text = StrBody
wdRng.collapse 0
wdRng.Paste
wdRng.collapse 0
wdRng.Text = StrBody1
.Attachments.Add FilePath
.Display
If bNoPreview Then
.Send
End If
End With
On Error GoTo 0
With Application
.EnableEvents = True
.ScreenUpdating = True
End With
Set OutMail = Nothing
Set OutApp = Nothing
Application.DisplayAlerts = True
Sheets("Email_Sheet").Cells(1, "A").Value = "Outlook sent Time, Dynamic msg preview count = " & i + 1
Next i
End With
Cleanup:
Set OutApp = Nothing
Set OutMail = Nothing
Set olInsp = Nothing
Set wdDoc = Nothing
Set wdRng = Nothing
Set rng = Nothing
lbl_Exit:
Exit Sub
End Sub
 
Back
Top