Help with VBA
 

I have the below code, how can I also add in a CC receipient from cell 6 in
the row

Sub SendEMail()
Dim Email As String, Subj As String
Dim Msg As String, URL As String
Dim r As Integer, x As Double
For r = 2 To 5
'
Email = Cells(r, 2)

' Message subject
Subj = "Overdue Fire Inspection Report"

' Compose the message
Msg = ""
Msg = Msg & "Sir/Ma'am, " & vbCrLf & vbCrLf
Msg = Msg & "Report for facility " & Cells(r, 3).Text & " is overdue
by "
Msg = Msg & Cells(r, 4).Text & " days." & vbCrLf & vbCrLf
Msg = Msg & "If you have any questions, please contact our office at
" & vbCrLf & vbCrLf & vbCrLf
Msg = Msg & "" & vbCrLf
Msg = Msg & "Prevention Section"

' Replace spaces with %20 (hex)
Subj = Application.WorksheetFunction.Substitute(Subj, " ", "%20")
Msg = Application.WorksheetFunction.Substitute(Msg, " ", "%20")

' Replace carriage returns with %0D%0A (hex)
Msg = Application.WorksheetFunction.Substitute(Msg, vbCrLf, "%0D%0A")
' Create the URL
URL = "mailto:" & Email & "?subject=" & Subj & "&body=" & Msg

' Execute the URL (start the email client)
ShellExecute 0&, vbNullString, URL, vbNullString, vbNullString,
vbNormalFocus

' Wait two seconds before sending keystrokes
Application.Wait (Now + TimeValue("0:00:02"))
Application.SendKeys "%s"
Next r
End Sub

Help with VBA
 

Try this method instead. It calls Outlook (change to Express if that's
what you use) using COM and thus gives you better control over the
process. Look for the line "someone.Type", which sets whether or not
it's a to, cc or bcc. No need to convert to HTML or anything like
that.

For r = 2 To 5
Email = Cells(r, 2)
if isEmpty(Email) then GoTo TRYNEXTROW ' ALWAYS do this!

Set outlookMsg = CreateObject("Outlook.Application").CreateItem(0)
' you can test here if it worked, but this normally throws errors if
it didn't

With outlookMsg
' add the addresses
Set someone = .Recipients.Add(email)
someone.Type = 1 ' 1="to", cc and bcc can also be made
someone.Resolve
' add more addresses here

' it's not a bad idea to test whether someone.Resolved is not null
' but I've never seen this happen when you use internet addresses

' title it and add the body
.Subject = Subj
.Body = Msg

' save and send!
.Save
.Send
End With
' all done, trash the object
Set outlookMsg = Nothing

TRYNEXTROW:
Next r

Help with VBA
 

I am under the impression that Outlook Express (nor its Vista equivalent,
Windows Mail) can be controlled that way using VB.

--
Rick (MVP - Excel)


"Maury Markowitz" wrote in message
...
Try this method instead. It calls Outlook (change to Express if that's
what you use) using COM and thus gives you better control over the
process. Look for the line "someone.Type", which sets whether or not
it's a to, cc or bcc. No need to convert to HTML or anything like
that.

For r = 2 To 5
Email = Cells(r, 2)
if isEmpty(Email) then GoTo TRYNEXTROW ' ALWAYS do this!

Set outlookMsg = CreateObject("Outlook.Application").CreateItem(0)
' you can test here if it worked, but this normally throws errors if
it didn't

With outlookMsg
' add the addresses
Set someone = .Recipients.Add(email)
someone.Type = 1 ' 1="to", cc and bcc can also be made
someone.Resolve
' add more addresses here

' it's not a bad idea to test whether someone.Resolved is not null
' but I've never seen this happen when you use internet addresses

' title it and add the body
.Subject = Subj
.Body = Msg

' save and send!
.Save
.Send
End With
' all done, trash the object
Set outlookMsg = Nothing

TRYNEXTROW:
Next r

Help with VBA
 

This keeps erroring at the .recipients.add

"Maury Markowitz" wrote:

Try this method instead. It calls Outlook (change to Express if that's
what you use) using COM and thus gives you better control over the
process. Look for the line "someone.Type", which sets whether or not
it's a to, cc or bcc. No need to convert to HTML or anything like
that.

For r = 2 To 5
Email = Cells(r, 2)
if isEmpty(Email) then GoTo TRYNEXTROW ' ALWAYS do this!

Set outlookMsg = CreateObject("Outlook.Application").CreateItem(0)
' you can test here if it worked, but this normally throws errors if
it didn't

With outlookMsg
' add the addresses
Set someone = .Recipients.Add(email)
someone.Type = 1 ' 1="to", cc and bcc can also be made
someone.Resolve
' add more addresses here

' it's not a bad idea to test whether someone.Resolved is not null
' but I've never seen this happen when you use internet addresses

' title it and add the body
.Subject = Subj
.Body = Msg

' save and send!
.Save
.Send
End With
' all done, trash the object
Set outlookMsg = Nothing

TRYNEXTROW:
Next r

Help with VBA
 

Hello Jason,

Thanks for using Microsoft Newsgroup Support Service, my name is Colbert
Zhou [MSFT] and I will be working on this issue with you.

Maury's codes are almost right and just need a little modification. Thanks
to Maury's input here.

We need to change the line
If IsEmpty(Email) then GoTo TRYNEXTROW ' ALWAYS do this!
to,
If IsEmpty(Cells(r, 2)) then GoTo TRYNEXTROW ' ALWAYS do this!

IsEmpty function is used to test if a cell is empty, not the cell's value.
So in this case, even if a cell is empty, the original codes IsEmpty(Email)
returns false. As a result the Recipients.Add() will throw an exception
because the parameter is an "" string.

Please replace Email with Cells(r,2) and test again. It should work.

At last, some clarification about the Outlook Express. The Express version
of Outlook does not expose as COM Server, so it cannot be automated. That
is to say, to run the above codes, we need the Office Outlook installed on
that machine.

Please let me know if you have any future questions or concerns on this.
And I will try my best to provide future help. Have a nice day, all!

Best regards,
Colbert Zhou (colbertz @online.microsoft.com, remove 'online.')
Microsoft Online Community Support

Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
.