Send email using spreadsheet

Status
Not open for further replies.
Y

YXR3b3Jr

Hello,

I am trying to send an email from a spread sheet. Below is what i have done

so far but what I want to do is select the values in A1 (A1 is the email

address) and send the email then select the value in A2 (A2 is the next email

address) and send the email and so on but i have to stop after sending 200

emails even though i have about 3000 in the excel spread sheet. The cc

address and bcc address will be the same every time.

Sub SENDMAIL()

Dim olApp As Object

Dim olNs As Object

Dim olMail As Object

Dim Email_Address

Dim ExcApp As Object

Dim Excval As String

Set ExcApp = Application.CreateObject("excel.application")

Set ExcWb = ExcApp.Workbooks.Open("C:\users\XXX\mailing\send.xls")

Excval = ExcWb.sheets("sheet1").Range("A1").Value

On Error Resume Next

Set olApp = GetObject(, "Outlook.Application")

Set olNs = olApp.GetNamespace("MAPI")

Set olMail = olApp.CreateItem(olMailItem)

With olMail

> To = Excval

> CC = "XXX@XXX.net"

'.BCC = "XXX@XXX.org"

> Subject = "We test and test"

> Body = "another test two"

> Attachments.Add "c:\users\XXX\Mailing\test.txt"

> Send

End With

Set olMail = Nothing

Set olApp = Nothing

End Sub
 
Where are you running this code? Outlook, or Excel? You are using

CreateObject and GetObject for both Excel and Outlook, so it isn't

clear where the code is being run.

You should be using each application's native object references. i.e.

if you are running this from Outlook, try

Dim olApp As Outlook.Application

Set olApp = Outlook.Application

And if you run it from Excel, it should be

Dim ExcApp As Excel.Application

Set ExcApp = Excel.Application

Also, what you probably want to do is create a Range Object consisting

of all the values in column A (I assume the email addresses start in

A1 and run down continuously in the same column without blanks). Use a

For Each loop to process the contents of each cell in the range.

HTH

On Mar 12, 9:10 pm, atwork <atw...> wrote:
> Hello,
> I am trying to send an email from a spread sheet. Below is what i have done
> so far but what I want to do is select the values in A1 (A1 is the email
> address) and send the email then select the value in A2 (A2 is the next email
> address) and  send the email and so on but i have to stop after sending200
> emails even though i have about 3000 in the excel spread sheet. The cc
> address and bcc address will be the same every time.
> Sub SENDMAIL()
>     Dim olApp As Object
>     Dim olNs As Object
>     Dim olMail As Object
>     Dim Email_Address
>     Dim ExcApp As Object
>     Dim Excval As String
>     Set ExcApp = Application.CreateObject("excel.application")
>     Set ExcWb = ExcApp.Workbooks.Open("C:\users\XXX\mailing\send.xls")
>     Excval = ExcWb.sheets("sheet1").Range("A1").Value
>      On Error Resume Next
>     Set olApp = GetObject(, "Outlook.Application")
>     Set olNs = olApp.GetNamespace("MAPI")
>     Set olMail = olApp.CreateItem(olMailItem)
>     With olMail
>         .To = Excval
>         .CC = "X...@XXX.net"
>         '.BCC = "X...@XXX.org"
>         .Subject = "We test and test"
>         .Body = "another test two"
>         .Attachments.Add "c:\users\XXX\Mailing\test.txt"
>         .Send
>     End With
>     Set olMail = Nothing
>     Set olApp = Nothing
> End Sub
 
JP,

I am running this from Outlook and you are correct the addresses start at A1

and run down the column. I am a little confused with setting up the loop.

"JP" wrote:


> Where are you running this code? Outlook, or Excel? You are using
> CreateObject and GetObject for both Excel and Outlook, so it isn't
> clear where the code is being run.

> You should be using each application's native object references. i.e.
> if you are running this from Outlook, try

> Dim olApp As Outlook.Application
> Set olApp = Outlook.Application

> And if you run it from Excel, it should be

> Dim ExcApp As Excel.Application
> Set ExcApp = Excel.Application

> Also, what you probably want to do is create a Range Object consisting
> of all the values in column A (I assume the email addresses start in
> A1 and run down continuously in the same column without blanks). Use a
> For Each loop to process the contents of each cell in the range.

> HTH

> On Mar 12, 9:10 pm, atwork <atw...> wrote:
> > Hello,
> > I am trying to send an email from a spread sheet. Below is what i have done
> > so far but what I want to do is select the values in A1 (A1 is the email
> > address) and send the email then select the value in A2 (A2 is the next email
> > address) and send the email and so on but i have to stop after sending 200
> > emails even though i have about 3000 in the excel spread sheet. The cc
> > address and bcc address will be the same every time.
> > Sub SENDMAIL()
> > Dim olApp As Object
> > Dim olNs As Object
> > Dim olMail As Object
> > Dim Email_Address
> > Dim ExcApp As Object
> > Dim Excval As String
> > Set ExcApp = Application.CreateObject("excel.application")
> > Set ExcWb = ExcApp.Workbooks.Open("C:\users\XXX\mailing\send.xls")
> > Excval = ExcWb.sheets("sheet1").Range("A1").Value
> > On Error Resume Next
> > Set olApp = GetObject(, "Outlook.Application")
> > Set olNs = olApp.GetNamespace("MAPI")
> > Set olMail = olApp.CreateItem(olMailItem)
> > With olMail
> > .To = Excval
> > .CC = "X...@XXX.net"
> > '.BCC = "X...@XXX.org"
> > .Subject = "We test and test"
> > .Body = "another test two"
> > .Attachments.Add "c:\users\XXX\Mailing\test.txt"
> > .Send
> > End With
> > Set olMail = Nothing
> > Set olApp = Nothing
> > End Sub


>
 
You'll want to set up the loop after the line of code that opens the

workbook. Here's some air code for that, it's not the way I described

earlier but it should get you started. This code simply selects each

cell in column A in turn, runs some code, then selects the next cell

down, and continues to do so until there are no more values in column

A.

Do While ActiveCell.Value <> ""

' your code here to send email, ActiveCell will contain email

address

ActiveCell.Offset(1,0).Select

Loop

The code assumes that when the workbook opens, cell A1 is selected

(which is usually the case in my experience).

As I mentioned earlier, since you are doing this from Outlook, you

have direct access to Outlook's object model, so you should be using

Outlook.Application, not GetObject, for your olApp variable. The

Outlook objects should be appropriately declared (Outlook.Application,

Outlook.Namespace, Outlook.MailItem, etc).

Unfortunately I have to step outside now but if you like I can also

post another version that uses a Variant to read all the email

addresses in one pass, which should make the code a bit faster.

--JP

On Mar 13, 10:36 am, atwork <atw...> wrote:
> JP,
> I am running this from Outlook and you are correct the addresses start atA1
> and run down the column. I am a little confused with setting up the loop.

> "JP" wrote:
> > Where are you running this code? Outlook, or Excel? You are using
> > CreateObject and GetObject for both Excel and Outlook, so it isn't
> > clear where the code is being run.

>
 
JP,

Thanks for all your input. I could not figure out how to stop the sending

after 200 messages using the do while loop. Therefore, I did some more

research and found another way to deal with the cells. Did I make the

appropriate change for the Outlook object model? Here is what I think I am

going to go with:

Sub SENDMAIL()

Dim olApp As Outlook.Application

Dim olNs

Dim olMail

Dim Email_Address

Dim ExcApp As Object

Dim Excval As String

Set ExcApp = Application.CreateObject("excel.application")

Set ExcWb =

ExcApp.Workbooks.Open("C:\users\dwesterfield\mailing\send.xls")

For i = 1 To 200

Excval = ExcWb.ActiveSheet.Cells(i, 1)

'On Error Resume Next

Set olApp = Outlook.Application

Set olNs = olApp.GetNamespace("MAPI")

Set olMail = olApp.CreateItem(olMailItem)

With olMail

> To = Excval

'.CC = "dwest@goeaston.net"

'.BCC = "membership@mcuf.org"

> Subject = "We test and test and test some more"

> Body = "Please read the attachment"

> Attachments.Add "c:\users\dwesterfield\Mailing\test.txt"

> Send

End With

Next

Set olMail = Nothing

Set olApp = Nothing

ExcApp.Quit

End Sub

"JP" wrote:


> You'll want to set up the loop after the line of code that opens the
> workbook. Here's some air code for that, it's not the way I described
> earlier but it should get you started. This code simply selects each
> cell in column A in turn, runs some code, then selects the next cell
> down, and continues to do so until there are no more values in column
> A.

> Do While ActiveCell.Value <> ""
> ' your code here to send email, ActiveCell will contain email
> address

> ActiveCell.Offset(1,0).Select
> Loop

> The code assumes that when the workbook opens, cell A1 is selected
> (which is usually the case in my experience).

> As I mentioned earlier, since you are doing this from Outlook, you
> have direct access to Outlook's object model, so you should be using
> Outlook.Application, not GetObject, for your olApp variable. The
> Outlook objects should be appropriately declared (Outlook.Application,
> Outlook.Namespace, Outlook.MailItem, etc).

> Unfortunately I have to step outside now but if you like I can also
> post another version that uses a Variant to read all the email
> addresses in one pass, which should make the code a bit faster.

> --JP

> On Mar 13, 10:36 am, atwork <atw...> wrote:
> > JP,
> > I am running this from Outlook and you are correct the addresses start at A1
> > and run down the column. I am a little confused with setting up the loop.
> > "JP" wrote:
> > > Where are you running this code? Outlook, or Excel? You are using
> > > CreateObject and GetObject for both Excel and Outlook, so it isn't
> > > clear where the code is being run.

> >

>
 
You'll want to change

Dim olNs

Dim olMail

to

Dim olNs As Outlook.Namespace

Dim olMail As Outlook.MailItem

It doesn't look like you're using the variable Email_Address anywhere

in your procedure, so I think you can safely remove it. And you

haven't declared variable i. Overall it looks correct, but you've

hardcoded the loop counter. When you add or remove email addresses

from column A, you'll need to revise the loop.

--JP

On Mar 14, 5:31 pm, atwork <atw...> wrote:
> JP,
> Thanks for all your input. I could not figure out how to stop the sending
> after 200 messages using the do while loop. Therefore, I did some more
> research and found another way to deal with the cells. Did I make the
> appropriate change for the Outlook object model? Here is what I think I am
> going to go with:

> Sub SENDMAIL()
>     Dim olApp As Outlook.Application
>     Dim olNs
>     Dim olMail
>     Dim Email_Address
>     Dim ExcApp As Object
>     Dim Excval As String
>     Set ExcApp = Application.CreateObject("excel.application")
>     Set ExcWb =
> ExcApp.Workbooks.Open("C:\users\dwesterfield\mailing\send.xls")
>         For i = 1 To 200
>         Excval = ExcWb.ActiveSheet.Cells(i, 1)
>  'On Error Resume Next
>         Set olApp = Outlook.Application
>         Set olNs = olApp.GetNamespace("MAPI")
>         Set olMail = olApp.CreateItem(olMailItem)
>             With olMail
>             .To = Excval
>             '.CC = "dw...@goeaston.net"
>             '.BCC = "members...@mcuf.org"
>             .Subject = "We test and test and test some more"
>             .Body = "Please read the attachment"
>             .Attachments.Add "c:\users\dwesterfield\Mailing\test.txt"
>             .Send
>         End With
>     Next
>     Set olMail = Nothing
>     Set olApp = Nothing
>     ExcApp.Quit
> End Sub
>
 
JP,

I will make those changes but I just wanted to say thanks for the excellent

help!!

"JP" wrote:


> You'll want to change

> Dim olNs
> Dim olMail

> to

> Dim olNs As Outlook.Namespace
> Dim olMail As Outlook.MailItem

> It doesn't look like you're using the variable Email_Address anywhere
> in your procedure, so I think you can safely remove it. And you
> haven't declared variable i. Overall it looks correct, but you've
> hardcoded the loop counter. When you add or remove email addresses
> from column A, you'll need to revise the loop.

> --JP

> On Mar 14, 5:31 pm, atwork <atw...> wrote:
> > JP,
> > Thanks for all your input. I could not figure out how to stop the sending
> > after 200 messages using the do while loop. Therefore, I did some more
> > research and found another way to deal with the cells. Did I make the
> > appropriate change for the Outlook object model? Here is what I think I am
> > going to go with:
> > Sub SENDMAIL()
> > Dim olApp As Outlook.Application
> > Dim olNs
> > Dim olMail
> > Dim Email_Address
> > Dim ExcApp As Object
> > Dim Excval As String
> > Set ExcApp = Application.CreateObject("excel.application")
> > Set ExcWb =
> > ExcApp.Workbooks.Open("C:\users\dwesterfield\mailing\send.xls")
> > For i = 1 To 200
> > Excval = ExcWb.ActiveSheet.Cells(i, 1)
> > 'On Error Resume Next
> > Set olApp = Outlook.Application
> > Set olNs = olApp.GetNamespace("MAPI")
> > Set olMail = olApp.CreateItem(olMailItem)
> > With olMail
> > .To = Excval
> > '.CC = "dw...@goeaston.net"
> > '.BCC = "members...@mcuf.org"
> > .Subject = "We test and test and test some more"
> > .Body = "Please read the attachment"
> > .Attachments.Add "c:\users\dwesterfield\Mailing\test.txt"
> > .Send
> > End With
> > Next
> > Set olMail = Nothing
> > Set olApp = Nothing
> > ExcApp.Quit
> > End Sub
> >

>
 
No problem, and good luck to you!

On Mar 15, 10:12 am, atwork <atw...> wrote:
> JP,
> I will make those changes but I just wanted to say thanks for the excellent
> help!!

> "JP" wrote:
> > You'll want to change

>
> > Dim olNs
> > Dim olMail

>
> > to

>
> > Dim olNs As Outlook.Namespace
> > Dim olMail As Outlook.MailItem

>
> > It doesn't look like you're using the variable Email_Address anywhere
> > in your procedure, so I think you can safely remove it. And you
> > haven't declared variable i. Overall it looks correct, but you've
> > hardcoded the loop counter. When you add or remove email addresses
> > from column A, you'll need to revise the loop.

>
> > --JP

>
 
Status
Not open for further replies.
Similar threads
Thread starter Title Forum Replies Date
J Add an Attachment Using an Array and Match first 17 Letters to Matching Template .oft to Send eMail Outlook VBA and Custom Forms 2
D Using a VBA Custom Form to Send Reoccurring Email Upon Task Completion Outlook VBA and Custom Forms 4
D send email from Excel using outlook template Outlook VBA and Custom Forms 3
Diane Poremsky How to Send Email When Traveling or Using Public Wi-Fi Using Outlook 0
H send reminder if no reply received on first or original email using macro Using Outlook 2
T Using a macro to send email to diffrent address Outlook VBA and Custom Forms 1
S Can't send messages using Outlook 2007 from a business email Using Outlook 3
SirFishAlot Send atachment with mass email using BCM, and Publisher 2013 BCM (Business Contact Manager) 1
M Unable to send email to gmail - using Outlook 2007 Using Outlook 3
J send email using template Outlook VBA and Custom Forms 8
R Using SendObject in Access to send an email Outlook VBA and Custom Forms 3
R Outlook 365 VBA AUTO SEND WITH DELAY FOR EACH EMAIL Outlook VBA and Custom Forms 0
J Macro to send email as alias Outlook VBA and Custom Forms 0
F Add a category before "Send an Email When You Add an Appointment to Your Calendar" Outlook VBA and Custom Forms 0
W Outlook 365 I am getting the "Either there is no default mail client" error when I try to send an email on excel Office 365 Using Outlook 1
Witzker Outlook 2019 Macro to send an Email Template from User Defined Contact Form Outlook VBA and Custom Forms 0
Geldner Tweak Junk Email Reporting tool to default to particular email on send? Using Outlook 3
M Outlook 365 refuses to send email Using Outlook 0
M Outlook, send to > mail recipient - results in plain text email Using Outlook 1
glnz O365 - How to send from acct 2 but showing email name from acct 1 as From - alias? Using Outlook 0
M Extract all links from Outlook email, send to Excel Using Outlook 2
T After I send a new email, it remains in the Draft folder Using Outlook.com accounts in Outlook 3
S Change VBA script to send HTML email instead of text Outlook VBA and Custom Forms 3
M ERROR: None of your email accounts could send to this recipient Using Outlook 2
ChrisK2 Send email to advertise@slipstick.com fails: "The group advertising isn't set up to receive messages from..." Using Outlook 3
R auto send email when meeting closes from a shared calendar only Outlook VBA and Custom Forms 2
X Unable to send an email from one account to another on same PC Using Outlook 2
M VBA to send reminder email if no response Using Outlook 13
M Can't send email in outlook.com Using Outlook 9
B When working on emails in a certain folder, when I hit reply or reply all, I would like it re always reply all and add an email address to send to Outlook VBA and Custom Forms 3
A Outlook - Send New 20 Attachments through Email Using Outlook 4
S Send email via SMTP - use transport rules to add to senders inbox (then rule to move to sent items Exchange Server Administration 1
R Prompt asking the user to send email to folder as *.msg file Outlook VBA and Custom Forms 1
D Is it possible to automatically send an email when it is moved to a folder? Exchange Server Administration 1
B VBA Help Email that will save as draft and send as attachment Outlook VBA and Custom Forms 3
A Automatically send email based on drop-down field? Outlook VBA and Custom Forms 2
K Macro Not Executing then send email from Explorer Outlook VBA and Custom Forms 3
A Block user to send emails to specific set of email ids Using Outlook 1
T Can't send email through connected account (outlook.live.com) - goes to Drafts folder Using Outlook.com accounts in Outlook 3
C Custom Application Form send Email to Another User Using Outlook 1
C Macro to send email after changing from address and adding signature Outlook VBA and Custom Forms 1
M Send an Email When a Reminder Fires Outlook VBA and Custom Forms 1
Diane Poremsky Send an Email When You Add an Appointment to Your Calendar Using Outlook 0
Diane Poremsky Send email to all addresses for one Contact Using Outlook 0
G "Delay Delivery" Email fails to send if opened while in Outbox Using Outlook 4
E Send a Reminder/Task to certain Email Recipient Using Outlook 5
Diane Poremsky Send an Email When a Reminder Fires Using Outlook 0
A Send email from BCM BCM (Business Contact Manager) 5
S Send email from excel based on a condition (outlook 2007) Outlook VBA and Custom Forms 1
V Outlook 2010 – Send email based on categories Using Outlook 2

Similar threads

Back
Top