Mail sending througg macro

Hi All,

I have a list of email address of about 1000 in an excel sheet. Now I have a content to send to all the email address. Even I can sent all the email at bulk. I need to send an email for every 2 minutes from the list. I am using MS office 2007 outlook. Is there any macro to do this?

Any macro for this?

not to step on any toes but this works also....it may be the same as the other but I dont know....you'll have to insert your smtp server name....

Sub SendEmail(ToName, SendFile)

    Dim EmailTo As String, MessageBody As String, SubjectLine As String
    Dim objmessage As Object
    Dim i As Integer
        
    Set objmessage = CreateObject("CDO.Message")
    objmessage.Subject = SubjectLine
    objmessage.from = "ENTER VALID EMAIL USER"
    objmessage.bcc = ""
    objmessage.To = ToName     'comes from calling procedure
 
    'objmessage.AddAttachment SendFile
    
    objmessage.Subject = "Enter Subject Line here"
    objmessage.TextBody = "PLEASE DO NOT REPLY TO THIS EMAIL. IF YOU HAVE QUESTIONS PLEASE CONTACT THE BUYER DIRECTLY. THANK YOU."
    objmessage.Configuration.Fields.Item _
    ("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
    objmessage.Configuration.Fields.Item _
    ("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "Insert your SMTP Server name here"
    objmessage.Configuration.Fields.Item _
    ("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
    objmessage.Configuration.Fields.Update
    objmessage.Send
    
End Sub

("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "Insert your SMTP Server name here"

that is probably not your SMTP server name....you have to go into outlook and look under account settings to find the actually server name....it will usually have SMTP in the name....not always....

sorry.....I guess I thought you had that part done....

you would have to create a loop the grabbed an email address, call the code I sent you with the email address as a parameter,wait 2 minutesm then keep going until you run out of email addresses...do you have any code that you have started/created?

where does the email addresses come from?....are they in an excel file?

The file will be in my desktop. In the sheet1 and the file name is Email.

so that would be a "Yes"....it's an Excel file....I can probably do some basic setup for you and let you sort of complete the rest....give me a bit time....

so here is a basic outline...this will determine how many emails there are in column 1 of the active sheet (sheet1)....it will then loop through each email address and go into the sendemail sub and "pass" it the email to name....you will have to come up with your email name....what you want the subject to be...and what goes in the body and the SMTP server....then it comes back to the main sub and waits for the time you requested....currently it's 12 second....you can change it 120 for 2 minutes....and it will continue until the last email name....I would suggest you practice this with just your name in the email list so if it doesn't work right you would send out a bunch of weird emails....good luck....

Public Sub SendEmails()

    Dim LastRow As Integer, x As Integer
    
    LastRow = Cells(65000, 1).End(xlUp).Row

    For x = 1 To LastRow
        EmailTo = Cells(x, 1).Value
        SendEmail EmailTo
        newMinute = Minute(Now())
        newSecond = Second(Now()) + 12
        waitTime = TimeSerial(newHour, newMinute, newSecond)
        Application.Wait waitTime
    Next

End Sub

Sub SendEmail(ToName)

    Dim EmailTo As String, MessageBody As String, SubjectLine As String
    Dim objmessage As Object
    Dim i As Integer
        
    Set objmessage = CreateObject("CDO.Message")
    objmessage.Subject = "Testing stuff"
    objmessage.from = "ENTER YOUR EMAIL ADDRESS HERE"
    objmessage.bcc = ""
    objmessage.To = ToName

    'objmessage.AddAttachment SendFile
    
    objmessage.Subject = ""

    objmessage.TextBody = "PUT TO INFOMATION FOR THE BODY OF THE EMAIL HERE"
    objmessage.Configuration.Fields.Item _
    ("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
    objmessage.Configuration.Fields.Item _
    ("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "ENTER YOUR SMTP SERVER HERE"
    objmessage.Configuration.Fields.Item _
    ("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
    objmessage.Configuration.Fields.Update
    objmessage.Send
    
End Sub

so, some basic questions....are you putting this in a module in the excel that has the emails?....are you on Sheet1?

so you need to click on the stop box for running macros....it's a little to the right of the start button, which looks like a right arrow....

so....you have the code sitting in module 1 of the excel file that has Sheet1 with email addresses in column 1....you have the VB editor and I would recommend pressing F8....this will step through the code and you can see what is happening....also...saying the code doesn't execute isn't real helpful....is there an error?....what does it do?..or not do?

wait 15 seconds (unless you changed it 120) then wait 120 seconds... .... it's probably waiting for the wait statement to complete.....

what is the error?