Sending emails from excel using VBA

[annux3]

Hey,
I am having difficulties with putting a VBA code together. The code should send personalized emails taking data from different excel cells. I also need a greeting line such as Hello! and beginning line such as "Here is the overview of the used service:" and after that VBA code should take only needed data for every company. And end with "Best wishes, etc".
My excel table looks something like this, email addresses and company are duplicates so it would be nice if the code knew to take only what belongs to certain company:


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Period[/TD]
[TD]Number[/TD]
[TD]Provider[/TD]
[TD]Service[/TD]
[TD]Count[/TD]
[TD]Duration[/TD]
[TD]Total[/TD]
[TD]Email[/TD]
[/TR]
[TR]
[TD]201504[/TD]
[TD]12345[/TD]
[TD]ETV[/TD]
[TD]Phone[/TD]
[TD]3[/TD]
[TD]13[/TD]
[TD]32[/TD]
[TD]example@example.com[/TD]
[/TR]
[TR]
[TD]201504[/TD]
[TD]54321[/TD]
[TD]ETV[/TD]
[TD]Phone[/TD]
[TD]2[/TD]
[TD]12[/TD]
[TD]23[/TD]
[TD]example@example.com[/TD]
[/TR]
[TR]
[TD]201504[/TD]
[TD]98765[/TD]
[TD]Tallink[/TD]
[TD]Phone[/TD]
[TD]8[/TD]
[TD]18[/TD]
[TD]98[/TD]
[TD]this@email.com[/TD]
[/TR]
[TR]
[TD]201504[/TD]
[TD]56789[/TD]
[TD]Tallink[/TD]
[TD]Phone[/TD]
[TD]7[/TD]
[TD]17[/TD]
[TD]71[/TD]
[TD]this@email.com[/TD]
[/TR]
</tbody>[/TABLE]




Final product should look like this:
First email
to example@example.com
Hello!
Here is the overview of the used service:
Period / Number / Provider / Count / Duration /
201504 / 900654 / ETV / 5 / 89 /
201504 / 900098 / ETV / 2 / 24 /
Best wishes,
My Name
*Signature preferably


Second email
to this@email.com
Hello!
Here is the overview of the used service:
Period / Number / Provider / Count / Duration /
201504 / 354864 / Tele2 / 7 / 64/
201504 / 354864 / Tele2 / 7 / 64/
Best wishes,
My Name
*Signature preferably


ETC.


This email needs to be sent in the beginning of every month. There are about 13-15 different emails - so 13-15 letters to be sent.
Any ideas?
Code that i found is provided in the comments, but it gives me this: Compile error: User-defined type not defined. Sub Mail_To_Friends() gets colored yellow.

 

[annux3]

Code that i found looks like this:




Sub Mail_To_Friends()
'Below Loop can be changed to While Loop or increase the limit (10) if your list has more than 10 mail ids
Dim SendTo As String
Dim ToMSg As String


For i = 1 To 10
SendTo = ThisWorkbook.Sheets(1).Cells(i, 1)
If SendTo <> “” Then
ToMSg = ThisWorkbook.Sheets(1).Cells(i, 3)
Send_Mail SendTo, ToMSg
End If
Next i
End Sub
Sub Send_Mail(SendTo As String, ToMSg As String)
Dim OutlookApp As Object
Dim OutlookMail As Object


Set OutlookApp = CreateObject(“Outlook.Application”)
Set OutlookMail = OutlookApp.CreateItem(0)


With OutlookMail
.to = SendTo
.CC = “”
.BCC = “”
.Subject = “Happy New Year”
.Body = ToMSg
.Display ' or just put .Send to directly send the mail instead of display
End With


Set OutlookMail = Nothing
Set OutlookApp = Nothing
End Sub






I have added few lines:


Dim Valitud As String
Dim Pakkuja As String
Dim Arv As String
Dim Kestvus As String


Valitud = ThisWorkbook.Sheets(3).Cells(i, 2)
Pakkuja = ThisWorkbook.Sheets(3).Cells(i, 3)
Arv = ThisWorkbook.Sheets(3).Cells(i, 4)
Kestvus = ThisWorkbook.Sheets(3).Cells(i, 5)
ToMSg = Valitud & Pakkuja & Arv & Kestvus 'This line should add up previous Strings - however i do not know if it does, because i cannot run the code