send a set of data available in the excel sheet for different recipient (individually)

[Bharathi]

Hello,

I am not good in coding and all but i love to work with it through excel since i am BPO professional Over the years I've gradually relied more and more on excel and found out that i use less and less of its capabilities. I've dabbled with writing my own macros and on occasion some VBA (but not much) and have found that I generally need a pointer. thanks to this site most of my sheets look and perform better. Now i need a solution from end which i couldn't seen ever in this forum , am not sure but as for as i searched i couldn't see anything. Ok let me coming to the issue now..

Topic is " I need to send a set of data available in the excel sheet for different recipient (individually)"

step 1. I have data for example in the range of A2:G12 in the attached sheet for your ref .

step 2. i can fix the sub and cc part so only the "to" part is variable please help me to fix this since its being bit complicated for me.

step 3:The data available in the range A2:G12 is variable but format is same.

Requirement

I need a individual mail though out look by clicking one button in excel for different customer based on their set of data which is available in the range A2:G12

Condition is only if the data is available in the range of A2:G12 then only the particular mail has to be send to the specific customer.

say example :

In our data base i have a customer name in the range of B2:B12, but we have three different customer with in this range and it is vary from time to time , for example assume customer as a , b and c .

a customer has some 5 products and b customer has 2 products and c has 5 products it is absolutely a variable one not a constant , sometimes we have some 5 customers with with different combination for range.

Here i have attached a macro sheet which i am currently using this is for creating a table of data for certain range
for only one customer not a individual customer.

i would like to have this kind of email for n of customers with their on set of data

My code is for current work sheet that i have attached is named as "customer mail"

[ Sub customermail_()
Dim oApp As Outlook.Application
Dim oMail As MailItem
Dim mailbody As String
Dim range As String
Dim sig  As String
sig = "M.Subramnaia Bharathi"
  mailbody = "<TABLE Border=""1"", Cellspacing=""0""><TR>" & _
  "</TR>"
 
 
 
  For i = 1 To Sheets(1).range("a1048576").End(xlUp).Row
 
  mailbody = mailbody & "<TR>" & _
  "<TD ><center>" & Sheets(1).range("a" & i).Value & "</TD>" & _
  "<TD><center>" & Sheets(1).range("b" & i).Value & "</TD>" & _
  "<TD><center>" & Sheets(1).range("c" & i).Value & "</TD>" & _
  "<TD><center>" & Sheets(1).range("d" & i).Value & "</TD>" & _
  "<TD><center>" & Sheets(1).range("d" & i).Value & "</TD>"
  Next i
Set oApp = New Outlook.Application
Set oMail = oApp.CreateItem(oMailItem)
With oMail
'to address should be for indiviudal email id ,this is difficulty area for me
.To = ""
.CC = "group"
.Subject = "Your product for this month"
.HTMLBody = "Hi<br><br>" & "<br>" & mailbody & "</Table><br> <br>Regards <br> <br>" & sig
.Display
End With
End Sub ]

The above code will create one outlook mail for one customer will all the customer data .

but i need n of mail has to be generated with their particulars in the body of the mail alone with one click

Please not that customer is n number and their products too.


As my knowledge i have given all the data so please help me in working out this.

please advice the full code rather than telling i need to modify something in the exiting code i have with me because i am not good in VBA as like you guys ...

I stay at your disposal....................

Thanks

 

[dan_l]

I think you want to split the workbook up by customer and then send the email.

 

[Bharathi]

Hi Dan,

No, as i have attached the excel work book and the sheet mentioned data is same . there is no separate workbook to be add for each mail. i just want the mail to be separate for each customer with their won data available in the sheet i have attached . if a customer is x then for x related data that needs to be organized as table in the body of the mail and that needs to be send. so that each customer in the colom "B", he are i have attached the separate mail copy for each customer but i want this needs to be done with one click of the command button.