PDA

View Full Version : [SOLVED] How to add table in the outlook via excel.



Jagdev
12-16-2014, 04:48 AM
Hi Experts

Could you please help me with the following issue.

I have a macro in place from where I can send email to many people via excel to outlook. There is no issues when the body of the mail if it is a plain text. I am not able to send the table data to the user with this code. Could you please help me with it.

The body of the mail is in E4 cell of the attached macro. Please let me know the way around for it. I am searching google from last 2 days, but fail to find concrete solution to it.

Regards,
Jaggi

westconn1
12-16-2014, 01:30 PM
you can make a table, as a string, using html then
.htmlbody = strtable

Jagdev
12-21-2014, 02:41 AM
you can make a table, as a string, using html then
.htmlbody = strtable

Hi Westconn1

Sorry for the late reply. Your suggestion for using HTML in the body worked fine. I stuck with one more amendment. I have used Sheet2 tab in the attached macro as a table in the outlook mail. What I am looking for is it possible to change the table data excluding heading for more than one mail. I have added few sample in RAW_DATA sheet in the attached macro. Is it possible for the macro to take the value from RAW_DATA sheet every time and update the table data for each email,

Regards,
JD

westconn1
12-21-2014, 01:06 PM
Is it possible for the macro to take the value from RAW_DATA sheet every time and update the table data for each email,yes of course, you could use a lookup table, elsewhere on the sheet, or on a separate sheet

Jagdev
12-21-2014, 09:10 PM
Hi Westconn1

Could you please help me with the code. I really appreciate your help.

Regards,
JD

gmayor
12-21-2014, 11:38 PM
The principles involved are similar to those described in http://www.gmayor.com/mail_merge_charts.htm. You need two ranges (which I have added to your worksheet) and I have changed the lines so that you can see what is happening in real time. I would also use the Inspector to edit the message directly. The only downside of that is that it must display the messages before sending them. You only actually need one of the macros, you can conditionally add the .Send line as required (see below).


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

Jagdev
12-21-2014, 11:54 PM
Hi Graham

Thanks for the code. I really appreciate your help a big thanks from my end. This fixed my issue which I am trying from last few weeks.

Regards,
JD

Jagdev
12-22-2014, 12:01 AM
Hi Graham

You have added few data in Email_Sheet. Do I have to follow the same exercise for all the records.

Regards,
JD

gmayor
12-22-2014, 12:31 AM
The macro uses the data on the first sheet, so you will have to include the extra fields if you want to use them in your e-mail, and include the extra rows in the named range. See my linked web page which covers the theory in a little more depth (though that is inserting a chart and not a worksheet range).

Jagdev
12-22-2014, 01:45 AM
Hi Graham

I tweak the macro to pull the data from RAW_DATA sheet. Thanks for sharing the link it is really useful.

I want to create the Data in the RAW_DATA sheet dynamic. Currently we have set the default limit for RAW_DATA i.e. from $A2 to $E6. I changed it now. Can we make it dynamic as in depend upon the data in the RAW_DATA sheet the record should set its limit. The count in this sheet should be same in the EMAIL_Sheet. I mean to say the Emails count to send should be the same in the data available in the RAW_Sheet.

Regards,
JD

gmayor
12-22-2014, 02:12 AM
You can use the Raw_Data sheet as your mergedata range, but if you do that you will need to include in Raw_Data the other fields you want associated with the e-mail (which is why I added the extra fields to sheet 1). As you will have to add that data somewhere in your worksheet, it seems sensible to look it up and add it to the Raw_Data file, and increase the size of the range.

As you want Raw_Data to be dynamic, then you will have to set the scope of the named range in code at the start of the main macro before using it to merge.

Jagdev
12-22-2014, 02:23 AM
Hi Graham

I just do not want the macro to bydefault create the number of email. Like if I set the range from A2-E10 as an example and the data in the RAW_DATA sheet is from A2-E6 then the macro also creates few more empty email to complete its limit from A2-E10. I just want the macro to create the number of email depend upon the count of entries available in the RAW_Data Sheet. The count of entries in the RAW_DATA sheet well vary every day from 30 - 80. The macro should create the email count depend upon the these entries.

Sorry for troubling you so much. Please help me to set trigger on the RAW_Data sheet.

Regards,
JD

gmayor
12-22-2014, 04:00 AM
You are missing my point. The MergeData named range must be limited to the range you want to work with. If you are using Raw_Data and the number of rows is dynamic, then you must set the named range in the macro to encompass only the rows with data e.g call the following macro before running the rest. You have also changed the loop for no obvious purpose that I can see?



Sub SetRange()
Dim xlSheet As Worksheet
Dim LastRow As Long, LastCol As Long
Dim rng As Range
Set xlSheet = Sheets("RAW_Data")
With xlSheet
LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
Application.Calculation = xlManual
Names("MergeData").Delete
ActiveWorkbook.Names.Add _
Name:="MergeData", _
RefersToR1C1:="=RAW_Data!R2C1:R" & LastRow & "C" & LastCol
Application.Calculation = xlAutomatic
End With
Set x;Sheet = Nothing
End Sub

Jagdev
12-22-2014, 09:25 PM
Hi Graham

Sorry for the confusion. I was trying to change the code before, but now undo it with the one you provided to me. I am calling the above macro before running the other mocaro, but the information in the mail is getting mixed up. Please find the version of macro attached and let me know where I am making error in the code.

Regards,
JD

gmayor
12-22-2014, 10:50 PM
As I said in my last two messages, you can either merge the data on the RAW_DATA page, (in which case you need to set the values of the table you wish to merge on Sheet 2 to reflect the columns of the MergeData range) or you can merge the data on the front page. Either way you must have ALL the fields for each record in the named range, as I showed in the first example I returned - which worked!

Jagdev
12-22-2014, 11:24 PM
Hi Graham

Sorry for the trouble, but I am getting confuse here. I have no issue with the concept of pulling data from RAW_Data sheet to Email_Sheet and then filling the table in Sheet2. For Merge data we have already created "Mergedata" in "New manger" before and now we are creating a macro to set the dynamic range.

When I trying to call the "SetRange" macro before the "Preview" macro the information in the outlook sets incorrect, but when I am trying without "SetRange" macro, the result is fine. I am really :banghead: what are we doing here and why we have "Mergedata" in the "New Manager" field in excel. I have attached the updated macro with the mail. Please let me know how we can set the range dyanamic and the output is as per our expectation. We are going with the file you have provided me yesterday.

Regards,
JD

Jagdev
12-22-2014, 11:45 PM
Oops sent you incorrect file..

gmayor
12-23-2014, 12:10 AM
OK, I see what you mean. The problem now is that you have the named range on the Raw_Data range, whereas with this arrangement you need it to be the range of e-mail fields on the first page. This means changing the SetRange macro to use that worksheet and range. See attached.

Jagdev
12-23-2014, 12:27 AM
Hi Graham

You are a champ!

Thanks for all your help and sorry for the trouble.

I was surfing from morning to check how can I set a default folder link for attachment in this macro.

I will place all my files which needs to be attached with the mail with the name in the RAW_Data sheet - Column A - Ref





Is it possible to check the folder with the Ref number and if the attach file is available attached it if not throw an error msg that the file is not available. I find - http://www.vbaexpress.com/forum/showthread.php?34801-Solved-Search-and-Attach-file-to-email-using-VBA but will it work with the code of this macro.

Really sorry for all the trouble.

Regards,
JD

gmayor
12-23-2014, 12:58 AM
The following function will respond with True or False. You can call it as required to determine whether the files are present.


Public Function FileExists(ByVal Filename As String) As Boolean
Dim lngAttr As Long
On Error GoTo NoFile
lngAttr = GetAttr(Filename)
If (lngAttr And vbDirectory) <> vbDirectory Then
FileExists = True
End If
NoFile:
Exit Function
End Function

Jagdev
12-23-2014, 01:59 AM
Thanks for the code Graham.

Is it possible to add a default link in the above example link in our macro -

Dim Pth As String

Pth = "C:\AA\" '<==== Change to suit your specification.

In the code -
.Attachments.Add Pth & Dir(Pth & Range("A" & i) & ".pdf")

It will follow this:
"I want to know how can I put a function to attach a file into the email, but need to search the file equal to a cell value and than if it is equal, attach the file to the email.

Something like: search pdf file named equal to text in A column, and than attach."

I am looking for something on similar note. Like on the particular Path the macro will search for a string mentioned in the column A of our RAW_Data sheet and if find attach it with the respective mail and if no throw error msg that the file is not available.

Regards,
JD

gmayor
12-23-2014, 05:43 AM
Your macro already defines the attachment path. All you now have to do is change the line


.Attachments.Add FilePath

to


If FileExists(FilePath) Then
.Attachments.Add FilePath
Else
MsgBox "The file " & FilePath & " does not exist at that location."
End If


and add the code I last posted to the module.

Jagdev
12-23-2014, 09:13 PM
Hi Graham

I added the code as per your instruction and add few sample file in the location mentioned in the macro.


I created 3 sample files with the name and saved them in the folder - C:\Users\Sinderjt\Desktop\New folder\

12340A130001
12340A130002
12340A130003


After running the code it throws file not available error. Ideally it should identity these files and should have attached it with the mail. My attach file is in .PDF format.

I have attached the updated macro with the post. Please let me know if I am making any error in it.

Regards,
JD

gmayor
12-23-2014, 10:37 PM
Your cell in Column B with the filepath does not include the filename, so you wilkl need to add it to the filepath statement e.g.


FilePath = .Cells(i, "B").Value & .Cells(i, "A").Value & ".pdf"

Jagdev
12-23-2014, 11:45 PM
Hi Graham

Thanks alot for all your help. Merry christmas and Happy New Year to you and your family.

Regards,
JD

gmayor
12-24-2014, 12:31 AM
You are welcome, and the same good wishes to you and yours.

Jagdev
05-28-2015, 02:15 AM
Hi Gmayor
Hope you are well!
Sorry for posting on such an old thread. I am checking is it possible with the macro we have created and I attached it with the mail. We have created attached macro to send individual email from the list of entries available in the RAW_DATA sheet/tab. Is it possible for the macro to send mail a single mail in case of repeated entries available in the Raw_Data sheet. I have highlighted the entries in the Raw_Data sheet which should go in a single mail in the body of the table should have these entries in tabular form captured.
Please let me know if I am unclear with the requirement.
Regards,
JD