Excel and Outlook --- plz help!

[ralfp]

Hello,
I am trying to create a spreadsheet that I can use to input some data and then create emails to different destinataries with different arays of data.

so far i got this far:
I need to be able to create outlook emails from a spreadsheet.

Sub Test()
Dim olApp As Object, olMail As Object

Set olApp = CreateObject("Outlook.Application")
Set olMail = olApp.CreateItem(0)
olMail.To = "Anyone@anywhere.com"
olMail.Body = Range("a1")

olMail.Display

AppActivate "Microsoft Excel"

End Sub


I got a couple issues:
1) how do i select more than 1 email address?
2)how do i select a wider range? I need to select a3:d21, but having issues doing so.

I would really apprecitae some help regarding this matter.

thanks!

AP

 

[Michael M]

Hi
For the first question, create a list somewhere else in the workbook and then refer to that in your mail to line.
In this case my list is in M7 to M100
Something like this

 For i = 7 To 100 'SELECTS NAMES FROM RAGE I7 TO I100
    If Sheets("YOUR SHEET NAME HERE").Range("M7").Value <> "" Then 'Change Sheet name to suit
        nameList = nameList & ";" & Sheets("YOUR SHEET NAME HERE").Range("M" & i).Value 'Change Sheet name to suit
    End If
Next
    Set Mail_Object = CreateObject("Outlook.Application")
        With Mail_Object.CreateItem(o)
            .Subject = "This is the subject"
            .To = nameList
            .Body = "This is the text in the body" 'Change comments to suit
            .Send
    End With
        Application.DisplayAlerts = False
Set Mail_Object = Nothing
End Sub

in answer to the 2nd question, replace

Range("a1")

with

Range("A3:D21")

 

[ralfp]

Hello Michael,
Thanks for your help.
However, for problem 2, when i change the range as you mentioned i get the following error message :

Error -2147467259 in run time execution.
The inferior limit of the table must equal 0
(I am translating this text from spanish, so it might not be a perfect match..)

any ideas of why this might be happening?

 

[ralfp]

Pleaaase help!

 

[jmthompson]

What are you trying to use the range for?

 

[ralfp]

I need to select B3:C21.

 

[ralfp]

Anytime I select a range that includes more than just 1 cell, I get the same error message.

 

[jmthompson]

to select that range, (in english) simply enter, you may need to change the ":" to a different symbol.

Range("A3:D21").Select

It is not generally necessary to select a range to perform an action, though. What do you want to do with the range once selected?

 

[ralfp]

I want to be able to copy that into an outlook message in order to send an email.
I tried incluiding ".Select", but it is not working.

This is the error message Im getting
"Array lower bound must be zero"

This is what i am trying to get to:


Sub BSNSCONFIRMATION()

Dim olApp As Object, olMail As Object

Set olApp = CreateObject("Outlook.Application")
Set olMail = olApp.CreateItem(0)
olMail.To = "Alagos55@enp.cl"
olMail.Body = Range("B3:c21:")

olMail.Display

AppActivate "Microsoft Excel"


End Sub

 

[jmthompson]

I believe you will need to concatenate your cells:

olMail.Body = Range("B3").Value & Range("C3").Value & Range("D3").Value & Range("B4").Value & Range("C4").Value...etc

now this will just combine everything into one long string. If you want line breaks, spaces, etc, you will need to build those in. If you're looking to insert the values as a table...I don't know how.