Copy set of data and send automail

[trprasad78]

Hi
I am not good in VBA,

i would like to send email with following information.

From Account : [I need option to choose from which account it has to go ]
.TO
.CC
.Subject
.body
above details given attached file.

I have attached sample file
Sheet1:due record
Sheet2:Customer Info
Sheet3:Out file

Question 1: As per below code only 3 email will send, But it has to send email till record is end.

Question 2 : In out put file i have pasted table as image , how to copy one customer record and past in to his email content. (Table from Sheet1:due record)

Please help me get done.
Thanks
prasad.


also i try with few vba code follows

Sub sendeMail()

Dim olApp As Outlook.Application
Dim olMail As Outlook.MailItem

For i = 2 To 4

    Set olApp = New Outlook.Application
    Set olMail = olApp.CreateItem(olMailItem)
   
    With olMail
    .To = Cells(i, 2).Value
    .CC = Cells(i, 3).Value
    .Body = Cells(i, 4).Value
    .Subject = Cells(i, 5).Value
    .Display
    ''.Attachments.Add "C:/abc.txt"
    ''.Send
       
    End With

    Set olMail = Nothing
    Set olApp = Nothing
   
Next


End Sub

 

[trprasad78]

guys waiting for your valuable inputs.

 

[Monty]

Mr Trprasad..

Please follow the code...hope this helps.. as per your requirment.

Sub test()
Dim OutApp As Object
Dim OutMail As Object
Dim strbody As String
Dim toemail As String
Dim ccemail As String
Dim lastrow As Long
Dim emailsubject As String
Dim rowtrack As Integer
Dim attachmentpath As String
Set OutApp = CreateObject("Outlook.Application")
lastrow = Range("A" & Cells.Rows.Count).End(xlUp).Row
For rowtrack = 2 To lastrow
Set OutMail = OutApp.CreateItem(0)

'To Address
toemail = Range("B" & rowtrack).Value

'CC address
ccemail = Range("C" & rowtrack).Value

'Email subject
emailsubject = Range("D" & rowtrack).Value

'Body of email
strbody = Range("E" & rowtrack).Value

'Attachment path
attachmentpath = Range("F" & rowtrack).Value & Application.PathSeparator & Range("G" & rowtrack).Value

On Error Resume Next
With OutMail
'To Email Address
.To = toemail

'CC Email Address
.CC = ccemail

'You can also specify BCC if required
'.BCC = ""

'Email Subject
.Subject = emailsubject

'Body of the email
.Body = strbody

'Email attachment
.Attachments.Add attachmentpath

.Send
End With
On Error GoTo 0
Next
Set OutMail = Nothing
Set OutApp = Nothing

End Sub


Dont forget to click on like button

 

[trprasad78]

Mr Trprasad..

Please follow the code...hope this helps.. as per your requirment.

Sub test()
Dim OutApp As Object
Dim OutMail As Object
Dim strbody As String
Dim toemail As String
Dim ccemail As String
Dim lastrow As Long
Dim emailsubject As String
Dim rowtrack As Integer
Dim attachmentpath As String
Set OutApp = CreateObject("Outlook.Application")
lastrow = Range("A" & Cells.Rows.Count).End(xlUp).Row
For rowtrack = 2 To lastrow
Set OutMail = OutApp.CreateItem(0)

'To Address
toemail = Range("B" & rowtrack).Value

'CC address
ccemail = Range("C" & rowtrack).Value

'Email subject
emailsubject = Range("D" & rowtrack).Value

'Body of email
strbody = Range("E" & rowtrack).Value

'Attachment path
attachmentpath = Range("F" & rowtrack).Value & Application.PathSeparator & Range("G" & rowtrack).Value

On Error Resume Next
With OutMail
'To Email Address
.To = toemail

'CC Email Address
.CC = ccemail

'You can also specify BCC if required
'.BCC = ""

'Email Subject
.Subject = emailsubject

'Body of the email
.Body = strbody

'Email attachment
.Attachments.Add attachmentpath

.Send
End With
On Error GoTo 0
Next
Set OutMail = Nothing
Set OutApp = Nothing

End Sub


Dont forget to click on like button

Thank you i try your vb code, set of data needs to be printed as shown in output sheet, i dont want to attach any file.

using following link: https://msdn.microsoft.com/en-us/li...deltoSendMailPart2_IntroductiontoSendingEMail

I got set of record taken from due record sheet and printed in email as HTML, but i set of table has to added in email based on email name.

please refer my attached file.

 

[trprasad78]

While sending email from Customer info sheet, when prepare first email from first record it has to match customer name with due record sheet and same set of record as to past in email and send.

difficult task is it has to match by customer name and copy those set of table into that particulate customer email draft.

Please refer out put sheet.

sorry for bad english

 

[Monty]

Mr TrPrasad.

Trying to understand your question..with the attached file by you.

Please follow my instructions to do your job easy.

1) For "Due Record" sheet create unique list as your out put and create a image and keep it ready for your email purpose...I meen select data and copy and paste as image link which will become dynamic forever as and when data changes.



Attached file for you...need to do some changes from your end like tab names as per your requirements.

So what does this macro does...

It will go through all the email id's and put the image as body message..
So my advise is keep everything in one page.

Email Ids in "A" column..And name of the sender in "B" column

and the pic on the same page anywhere on the page macro will pic it up.




Hope this helps..

Let me know any challenges...Happy to help you.

You can always click on like...if you like...Monty

 

[Monty]

Mr TR Prasad.

Any luck?

 

[trprasad78]

Mr TR Prasad.

Any luck?

Thank you so much

HI Its working fine , but its attaching same jpg file.

which showed you some 5 emails to send, but in actual it have some 1000 email in one shot.

it has to past the table in each email .

In your above code it attaching same file for all emails.

next we need to avoid manually creating jpg file. code as to create file and paste in email and send automatically.

Thank you again.

 

[trprasad78]

@Monty

Still i am not clear , How the picture change dynamic based on "Customer info" sheet.

My experiment

copy past few records in one sheet and Defined Names for that range. with following formula =OFFSET('Due Record'!$I$3,0,0,COUNTA('Due Record'!$I$3:$I$23),6)

when ever data change in that range picture changed automatically.

But how the source data change automatically and how it will attach respective matching names in "customer info" sheet.

I tested your code creating Mypic.jpg in D:\ and run your code, email composing with same jpg.

Please help me how picture change dynamically.

Thanks
Prasad.