Mailing with Excell vbscript help

[fotelefth]

Hi im new to vba.
I have an Excel sheet, one column with emails and another with Subjects (for the emails). Im trying to send emails to the different recipients with different subjects e.g.:

= |=====A======|===B===
1 |mail1@mail.com | subject1
2 |mail2@mail.com | subject2
3 |mail3@mail.com | subject3

I have the following code:

[VBA]Set OutApp = CreateObject("Outlook.Application")
For Count = 1 To 3 //Assuming there are only 3 rows. But how can i make it loop till the end of the rows?
Set objMessage = OutApp.CreateItem(0)
objMessage.Subject = "Mail to" & ActiveSheet.Cells(Count, 1)//e.g. Email to mail1@mail.com must have a subject "Mail to subject1"
objMessage.Bcc = ActiveSheet.Cells(Count, 2) //Bcc is required
objMessage.Body = "This is some sample message text."
objMessage.Send
Next[/VBA]

But i'm getting an "Outlook does not recognize one or more names" error.

Any help with this code?

[Bob Phillips]

Maybe the email addresses are wrong.

[fotelefth]

Hmm, no they aren't. I've double-triple checked them before i post here.
The problem is with the code or mis-configuration.

[georgiboy]

Might just be me but i don't see where you are setting the recipient to. I see Subject/Bcc/Body & send (but no one to send to. Maybe try this.

[VBA]Sub SendEmail()
Dim OutlookApp As Object
Dim MItem As Object
Dim cell As Range
Dim email_ As String
Dim subject_ As String
Dim Bcc_ As String
Dim body_ As String
'Dim attach_ As String

'Create Outlook object
Set OutlookApp = CreateObject("Outlook.Application")

' Loop through the rows
For Each cell In Columns("A").Cells.SpecialCells(xlCellTypeConstants)


email_ = cell.Value 'this will need to be a valid email address (main recipient)
Bcc_ = cell.Offset(0, 1).Value 'this will need to be a valid email address (it's not at the moment)
subject_ = cell.Offset(0, 1).Value
body_ = "Hello" & vbNewLine & "World"


'Create Mail Item and send it
Set MItem = OutlookApp.CreateItem(0)
With MItem
.To = email_
.Bcc = Bcc_
.Subject = subject_
.Body = body_
'.Display
.Send
End With

Next
End Sub[/VBA]

Hope this helps

[fotelefth]

Wow thanks for your reply georgiboy, very informative.
Im gonna try it soon, thanks a lot :tu:

[fotelefth]

Damn, same error again . Maybe its some configuration with Outlook. Anyway thanks for your help.

[georgiboy]

your bcc is not a valid email address, does it work if you take out bcc?

[VBA]Sub SendEmail()
Dim OutlookApp As Object
Dim MItem As Object
Dim cell As Range
Dim email_ As String
Dim subject_ As String
'Dim Bcc_ As String
Dim body_ As String
'Dim attach_ As String

'Create Outlook object
Set OutlookApp = CreateObject("Outlook.Application")

' Loop through the rows
For Each cell In Columns("A").Cells.SpecialCells(xlCellTypeConstants)


email_ = cell.Value 'this will need to be a valid email address (main recipient)
'Bcc_ = cell.Offset(0, 1).Value 'this will need to be a valid email address (it's not at the moment)
subject_ = cell.Offset(0, 1).Value
body_ = "Hello" & vbNewLine & "World"


'Create Mail Item and send it
Set MItem = OutlookApp.CreateItem(0)
With MItem
.To = email_
'.Bcc = Bcc_
.Subject = subject_
.Body = body_
'.Display
.Send
End With

Next
End Sub [/VBA]

[fotelefth]

In fact, i have to use ONLY Bcc not To. I made the following changes
-Bcc_ = cell.Value
-commented To_

but still didnt work

[georgiboy]

Does it fail on every email address if you add the error line?
[VBA]Sub SendEmail()
Dim OutlookApp As Object
Dim MItem As Object
Dim cell As Range
Dim subject_ As String
Dim Bcc_ As String
Dim body_ As String

'Create Outlook object
Set OutlookApp = CreateObject("Outlook.Application")

' Loop through the rows
For Each cell In Columns("A").Cells.SpecialCells(xlCellTypeConstants)

On Error Resume Next

Bcc_ = Trim(cell.Value)
subject_ = cell.Offset(0, 1).Value
body_ = "Hello" & vbNewLine & "World"

'Create Mail Item and send it
Set MItem = OutlookApp.CreateItem(0)
With MItem
.Bcc = Bcc_
.Subject = subject_
.Body = body_
.Send
End With

Next
End Sub[/VBA]

[fotelefth]

Finally it worked! I did something wrong the first time i edited your version georgiboy. Thanks a lot much appreciated your help!

[georgiboy]

You can take out the .Send and replace it with .Display then you can see what it's trying to send.