• 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.

Sending Personalised mail From excel

S

sridhar_shri03

New Member
Hiiiii


i have an issue if solved much appreciated


I am an BPO Employee i have to send monthly ratings to my team members, by personalised mails to each and every one in my team with the data available in excel


for Ex:

In column A i have name of the team members and in Column B i have their e mail addresses, and their performance ratings in C,D,E,F like that, and i need to send mails to each and every one from my excel with their name followed by a common msg in all mails and followed by a table consist of data available in each row for that receipent with the data avaialable in column C,D,E,F with the header for the table as first row, when i run the macro i need to send the mails to all receipents available in the excel.


I have tried some codes which helped me to some extent but i cant able to get the table with the data available in column C,D,E,F for each receipent


Below is the Vba code worked some extent

`

Private Declare Function ShellExecute Lib "shell32.dll" _

Alias "ShellExecuteA" (ByVal hwnd As Long, ByVal lpOperation As String, _

ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, _

ByVal nShowCmd As Long) As Long

Sub SendEMail()

Dim Email As String, Subj As String

Dim Msg As String, URL As String

Dim r As Integer, x As Double

For r = 2 To 4 'data in rows 2-4

' Get the email address

Email = Cells(r, 2)


' Message subject

Subj = "Your Annual Bonus"


' Compose the message

Msg = ""

Msg = Msg & "Dear " & Cells(r, 1) & "," & vbCrLf & vbCrLf

Msg = Msg & "I am pleased to inform you that your annual bonus is "


Msg = Msg & Cells(r, 3).Text & "." & vbCrLf & vbCrLf

Msg = Msg & "William Rose" & vbCrLf

Msg = Msg & "President"


' Replace spaces with %20 (hex)

Subj = Application.WorksheetFunction.Substitute(Subj, " ", "%20")

Msg = Application.WorksheetFunction.Substitute(Msg, " ", "%20")


' Replace carriage returns with %0D%0A (hex)

Msg = Application.WorksheetFunction.Substitute(Msg, vbCrLf, "%0D%0A")

' Create the URL

URL = "mailto:" & Email & "?subject=" & Subj & "&body=" & Msg


' Execute the URL (start the email client)

ShellExecute 0&, vbNullString, URL, vbNullString, vbNullString, vbNormalFocus


' Wait two seconds before sending keystrokes

Application.Wait (Now + TimeValue("0:00:02"))

Application.SendKeys "%s"

Next r

End Sub

'

Please help me and save my life


Thanks
 
Hi, sridhar_shri03!

I didn't check the code, but if your problem is to attach the data in columns C:F just do this:

1) after the last Dim, add a new line and type:

Dim sColumns as string

2) after getting the email address (EMail = Cells...), add a new line and type:

[pre]
Code: 
sColumns = Cells(1,3).Value & CStr(Cells(r,3).Value) & " - " & _
Cells(1,4).Value & CStr(Cells(r,4).Value) & " - " & _
Cells(1,5).Value & CStr(Cells(r,5).Value) & " - " & _
Cells(1,6).Value & CStr(Cells(r,6).Value
[/pre]
3) before the signature, add a new line and type:

Msg = Msg & sColumns & vbCrLf & vbCrLf

That should add the header of each column and the data of the related row.

If any of the columns has just text, you can omit the associated CStr function in 2)

Regards!
 
Thanks for your response


But the code didn't work, i need to get a table with its header as first row and tables another row as information available in each row for that receipient ...


Pls help ...
 
Hi, sridhar_shri03!

Can you please upload a sample file and the design you want for the mails to be send? Thanks.

Regards!
 
Hi, sridhar_shir03!

I've just read the topic http://chandoo.org/forums/topic/how-to-get-character-in-vba

Maybe it's related to this one?

If it is, wouldn't it be better to unify them?

I let you this link http://chandoo.org/forums/topic/phd-forum-posting-rules-etiquette-pls-read-before-posting

It's the third of the sticky topic that you find at the main page of this forums, and in the 14th. paragraph it talks about cross posting.

"Cross Posting, generally it is considered poor practice to cross post, that is to post the same question on several forums in the hope of getting a response quicker.

If you do cross post, please put that in your post.

Also if you have cross posted and get an answer elsewhere, have the courtesy of posting the answer here so other readers can learn from the answer also, as well as stopping people wasting there time on your answered question."

If the topic firstly allowed by me in this post doesn't involve the same VBA code, I apologize for the misunderstanding.

Regards!
 
Sorry for the incovenience


I am new to the site i dont know how to upload the files


the following is the mail format i need to get


Hi Sridhar


U will get the bonus


Bonus productivity rating 1 rating 2

400 4000 5 4


(the above as table)


Regards

Sridhar


The excel file will have the data like this


Name Email address Bonus productivity rating1 rating 2

Sridhar Sridhar_shri03@in.com 400 4000 4 5

Sridhar shriloveguy03990@gmail.com 300 4500 3 5


Pls resolve this issue


Thanks

Sridhar
 
Hi, sridhar_shri03!

Thanks for your info, I'll try to find out what didn't worked before.

And just fyi give a look at this: it's the second sticky post in the main page at this forums:

http://chandoo.org/forums/topic/posting-a-sample-workbook

Regards!
 
Hi, sridhar_shri03!


In the "sColumns = ..." statement was missing the last character ")", a copy/paste error.

I changed it to display your data in this way:


-----

Dear Sridhar,


I am pleased to inform you that your annual bonus is 400.


Bonus: 400 - productivity: 4000 - rating1: 4 - rating2: 5


William Rose

President

-----


If this is suitable for your job, just enter the "sColumns = ..." statement as follows:

[pre]
Code: 
sColumns = Cells(1, 3).Value & ": " & CStr(Cells(r, 3).Value) & " - " & _
Cells(1, 4).Value & ": " & CStr(Cells(r, 4).Value) & " - " & _
Cells(1, 5).Value & ": " & CStr(Cells(r, 5).Value) & " - " & _
Cells(1, 6).Value & ": " & CStr(Cells(r, 6).Value)
[/pre]
If you want the data to be displayed as:

Title Title Title Title

Value Value Value Value

... just reorder the Cells(x,y).Value parts from previous statement to proper order, but take care of value lenghts and space accordingly.


Regards!
 
Hi SirJB7


Thanks for your response ...


I want the data to be displayed in table as:

Title Title Title Title

Value Value Value Value


I tried reording the statements but i am not able to get the output


Could your pls help


Thanks

Sridhar
 
Hi, sridhar_shri03!

Try with this "sColumns = ..." statement:

[pre]
Code: 
sColumns = Left$(Cells(1, 3).Value & Space$(20), 20) & _
Left$(Cells(1, 4).Value & Space$(20), 20) & _
Left$(Cells(1, 5).Value & Space$(20), 20) & _
Left$(Cells(1, 6).Value & Space$(20), 20) & vbCrLf & _
Left$(Cells(r, 3).Value & Space$(20), 20) & _
Left$(Cells(r, 4).Value & Space$(20), 20) & _
Left$(Cells(r, 5).Value & Space$(20), 20) & _
Left$(Cells(r, 6).Value & Space$(20), 20)
[/pre]
Here you have a sample file:

http://www.2shared.com/file/aC1uGb9x/Sending_Personalised_mail_From.html

Regards!
 
Hi SirJB7


Thanks for your response


But i want the table as format in the below picture


http://www.mediafire.com/download.php?hmwcnv616a5w6a5


Pls help


Thanks

Sridhar
 
Hi, sridhar_shri03!

Sorry, but I don't know how to do that.

I can only suggest you to change mail font to a fixed width one, or adjust manually the field lenghts hardcoded to 20 in the "sColumns = ..." statement.

Regards!
 
You must log in or register to reply here.
Back
Top