Email Sender VBA Outlook

Email Sender VBA Outlook is an emailing program that sends emails by communicating with Microsoft Outlook.

Explanation

Email Sender VBA Outlook is a VBA Excel program that communicates with Microsoft Outlook. The program sends email templates from a predefined place on you computer, the file needs to be an .oft-file. There is also a function for blocking certain email addresses. The program requires the reference “Microsoft Outlook XX.X Object Library” to be enabled. In the new version of office it is easy to block communication between excel and outlook make sure to enable the communication before trying the code otherwise it will not work.

The entire VBA Excel program is available for downloading at the bottom of this page, enjoy!

Code

Sub Email_Sender_VBA_Microsoft_Outlook()

Dim NoMailList(1500)
Call LoadNoMailList(NoMailList)

WaitTimeSecondsBetweenMail = Range("c4").Value
PlaceToStoreEmailTemplate = Range("c5").Value

RowA = 0
While Range("A14").Offset(RowA, 0).Value <> tom
    ToAdress = Range("c14").Offset(RowA, 0).Value
    Subject = Range("d14").Offset(RowA, 0).Value
    FileName = Range("D14").Offset(RowA, 0).Value
    Call WaitTimeProgram(WaitTimeSecondsBetweenMail)
    Subject = Range("e14").Offset(RowA, 0).Value
    Call MatchAdressWithNoMailList(ToAdress, Funnen, NoMailList)
    If Funnen = False Then
        Call EmailSenderProgram(ToAdress, FileName, Subject, PlaceToStoreEmailTemplate)
    End If
    RowA = RowA + 1
Wend

End Sub

Sub EmailSenderProgram(ToAdress, FileName, Subject, PlaceToStoreEmailTemplate)

Dim VBAOutlookEmailSend As Object, vItem As Object, vStr As String
Set VBAOutlookEmailSend = CreateObject("Outlook.Application")
Dim temp2 As String
temp2 = FileName
Set vItem = VBAOutlookEmailSend.CreateItemFromTemplate(PlaceToStoreEmailTemplate + temp2 + ".oft")
vItem.Subject = Subject
Dim ToContact As Outlook.Recipient
Set ToContact = vItem.Recipients.Add(ToAdress)
vItem.ReadReceiptRequested = False
vItem.Send
Set vItem = Nothing
Set VBAOutlookEmailSend = Nothing

End Sub

Public Sub LoadNoMailList(NoMailList)

rad = 0
While Range("g14").Offset(rad, 0).Value <> tom
    NoMailList(rad + 1) = Range("g14").Offset(rad, 0).Value
    rad = rad + 1
Wend

End Sub

Public Sub MatchAdressWithNoMailList(ToAdress, Funnen, NoMailList)

Funnen = False
plats = 1
While NoMailList(plats) <> tom
    komp = InStr(ToAdress, NoMailList(plats))
    If komp <> 0 Then Funnen = True
    plats = plats + 1
Wend

End Sub

Public Sub WaitTimeProgram(sek)

newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + sek
waitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait waitTime

End Sub

Download excel file! Email_Sender_VBA_Outlook.xls

Comments (4)

Hi, I am in need of vba code to email from Excel as follows:
I have a workbook that that has TWO data validation drop down selection boxes. These have a number of names to select from. These are in B5 & B6.

I have the email addresses for each of these names stored in cells H9 thru' to H38.

I have a button called email. When this is pressed I need the code to email the whole workbook (which is only sheet1)to the email addresses corresponding to the two selections from the drop down boxes. It also needs an option to send to the address stored in H30 as well.

I need them to all be on the same line (not cc'd in the usual cc & bcc fields).

It should prompt with a question if I want to save first. Yes or No.
With a do this or do that answer. I have code already running to handle the various saving options so if it just has the question message box I can put my code in place of the answers.

The body of the email needs to include my signature & up to about 300 characters of text. Can this be drawn from info in another cell?
For my signature can the email just use my usual Outlook stored signature? I've tried a couple of simple email macros but the body of the email is just blank.

The Subject line should be the text that's in Cell B177.

The email format should be html to preserve the formatting.

I am not a programmer of any sorts. Quite computer illiterate but have managed to build my excel file using macro's that I've searched for & built on that. So I need code that I can just copy & paste. I assume I would add it into my modules. Is what I'm asking for doable & am I asking too much from a favour? I have tried copying & pasting some macros that only result in error messages yet others seem to work fine.

I'm using XP Sp2 & Microsoft Office 2003.
Any help of course I'd be extremely grateful for.

Thanks in advance.
Howard Christian.

Sorry for this tom is empty in swedish. It is a variable containing nothing!

Hi. Thanks for your interesting posts.

However I have some difficulties when running the VBA. This is probably due to the original language it was written (German?).

Let's take an instruction: "While Range("A14").Offset(RowA, 0).Value tom" seems to refer to a variable "tom". I suppose that this is some kind of "system variable", but in german(?) vba.

What would be the equivalent of "tom" in "english vba"?

Thanks, Matias (from Brazil)

Perfect.
Thanks.

Add your comment

yvComment v.CommentVersion