Crate email from Excel - Pull data from excel and Create email message

**saravanan1981** · 09-18-2016, 07:35 AM

Hi,
I have 2 sheets 1st (Data) and 2nd is (Name and email id)

1 Filter data in column G.(Filter Criteria is Begins with "Sent on" and "Resolved"). (In this cases there will be 35 filtered rows.)
2 Open a email message
3 Then Copy data from A to E and paste in email body. (In this case cell number from A1 to E58.)
4 There should be a proper serial number in column A

5 Again in same filtered rows filter one more criteria that is in column F. in Column F filter "Yes".
6 Then Copy data from A to E and paste in email body below the above data. (In this case cell number from A1 to E58.)
7 There should be a proper serial number in column A
8 Pull email id 's from column C of Sheet (Name and email id), from to, cc and BCC columns
9 Paste Kind attention names in email body copying from column F of Sheet (Name and email id)

I wanted to repeat the same for all companies mentioned from cell no. G1 to AJ1 mentioned in the sheet (Data)
Means I want to create 30 individual emails as per above filter data based on company.

I have attached excel file and example email message.

**saravanan1981** · 09-18-2016, 07:52 AM

I am trying to change the below code. but it not working

Sub Send_Email()

Dim EmailSubject As String
Dim SendTo As String
Dim EmailBody As String
Dim ccTo As String

EmailSubject = "Test"
SendTo = "[email protected]"

FirstRow = 1
LastRow = 5
FirstCol = 1
LastCol = 2

For r = FirstRow To LastRow
For c = FirstCol To LastCol

For Each cell In Cells(r, c)
strtable = strtable & " " & cell.Value
Next
Next
strtable = strtable & vbNewLine
Next

EmailBody = "Hi" & vbLf & vbLf & " body of message you want to add" & vbLf & vbLf & strtable & vbNewLine

Set App = CreateObject("Outlook.Application")
Set Itm = App.CreateItem(0)
With Itm
.Subject = EmailSubject
.To = SendTo
.CC = ccTo
.Body = EmailBody
.Display
'.Send
End With
Set App = Nothing
Set Itm = Nothing
End Sub

**saravanan1981** · 09-18-2016, 12:35 PM

Dear Friends,
Somehow I managed to complete my code upto 90%. I just need your help on few things.

In continuation with my existing code in attachment

I want code to filter the excel data further one more criteria in Sheet(Data), Range F1 and Criteria is “Yes”

Copy a range Sheet(Data), Range A1:F58 and paste below strbody1

Please help

**jaslake** · 09-20-2016, 12:18 PM

Hi saravanan1981

Try this Code in the attached (the RangeToHTML Code is also in the Workbook)...

Please Login or Register  to view this content.

Crate email from Excel - Pull data from excel and Create email message

LinkBack

Thread Tools

Rate This Thread

Display

Crate email from Excel - Pull data from excel and Create email message

Re: Crate email from Excel - Pull data from excel and Create email message

Re: Crate email from Excel - Pull data from excel and Create email message

Re: Crate email from Excel - Pull data from excel and Create email message

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] VBA to send email using data from the EXCEL including email address, subject and body text

email data from excel or create a word document

From Excel VBA, can I FORWARD an outlouk email? (not create a NEW email)

vba pull data from outlook body of email through email or subject of mail into excel

Copy data from excel to Outlook new email and create draft.

How to parse data in Outlook email using HTML version of email back to excel

Send data from Excel in email from specific email account

Bookmarks

Bookmarks

Posting Permissions