Macro problem: Won't send messages

[Gina_28 via OfficeKB.com]

Hello,

I have a macro that is pulling information from an Excel sheet, and for each
row, it opens a new Outlook mail message, with the specific information for
each person based on a general template for the message. The macro creates
the messages perfectly, and pulls in the information accurately, but will not
send the messages. So, I run my macro,and I now have 50 messages at the
bottom of my screen that I have to pull up and hit send for each one. Am I
missing a partial code that I need to add to the end of my macro? Or could
it be a firewall that is preventing it from sending?

Thank you very much!
Gina

 

[JLGWhiz]

Hard to tell, can't see your code. Maybe you can find an answer at this site.

http://www.rondebruin.nl/sendmail.htm

 

[NoodNutt]

G'day Gina

Try this link:

http://www.rondebruin.nl/mail/folder1/mail4.htm

Compare your code against Ron's

HTH
Mark.

 

[Gina_28 via OfficeKB.com]

Thank you! I'm at that site right now, searching for just this piece, but
I'm still new to macros so ...kinda searching in the dark. Here is the code
I'm working with....

Sub Test()
Dim OutApp As Object
Dim OutMail As Object
Dim cell As Range

Application.ScreenUpdating = False
Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon

On Error GoTo cleanup
For Each cell In Sheets("Sheet1").Columns("B").Cells.SpecialCells
(xlCellTypeConstants)
If cell.Value Like "?*@?*.?*" And LCase(cell.Offset(0, 1).Value) =
"yes" _
And LCase(cell.Offset(0, 2).Value) <> "sent" Then
Set OutMail = OutApp.CreateItem(0)

On Error Resume Next
With OutMail
.to = cell.Value
.Subject = "Welcome to Company"
.Body = "Dear " & cell.Offset(0, -1).Value & vbNewLine &
vbNewLine & _
"We are excited that you are joinging our team, and
felt that your qualifications might be a great fit for a position that I
currently have available. Based upon the information we have regarding your
compensation and benefit needs, I am confident that we have a position that
meets or exceeds your expectations." & vbNewLine & vbNewLine & _
"I would like to invite you to schedule yourself to
discuss the opportunity further. The link below will allow you to schedule
yourself during a time that is suitable for you. When you begin to enter your
contact information, please reference the Taleo # 9999999. Please expect a
call at the time that you choose with the primary phone number you provide."
& vbNewLine & vbNewLine & _
"https://website" & vbNewLine & vbNewLine & _
"I invite you to learn more about us at
www.company.com, and look forward to connecting with you soon." & vbNewLine &
vbNewLine & _
"Thank You," & vbNewLine & vbNewLine & _
"Hiring Manager" & vbNewLine & _
"Regional Recruiter"
.Importance = 2 'High importance
.ReadReceiptRequested = True

'You can add files also like this
'.Attachments.Add ("C:\test.txt")
.display
Application.Wait (Now + TimeValue("0:00:02"))
Application.SendKeys "%S%g%g"
Application.Wait (Now + TimeValue("0:00:02"))


'Or use Display
End With
On Error GoTo 0

cell.Offset(0, 2).Value = "sent"
Set OutMail = Nothing
End If
Next cell

cleanup:
Set OutApp = Nothing
Application.ScreenUpdating = True
End Sub

Hard to tell, can't see your code. Maybe you can find an answer at this site.

http://www.rondebruin.nl/sendmail.htm

Hello,

[quoted text clipped - 9 lines]

Thank you very much!
Gina

 

[Ron de Bruin]

Try to change this

.display
Application.Wait (Now + TimeValue("0:00:02"))
Application.SendKeys "%S%g%g"
Application.Wait (Now + TimeValue("0:00:02"))

to

..Display
Application.Wait (Now + TimeValue("0:00:02"))
Application.SendKeys "%S"


But Sendkeys is not always working OK
Be sure when you test it you close the VBA editor

Another option is to use Excel/Outlook 2007
There are no security warnings then or use CDO
http://www.rondebruin.nl/cdo.htm






--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm

Thank you! I'm at that site right now, searching for just this piece, but
I'm still new to macros so ...kinda searching in the dark. Here is the code
I'm working with....

Sub Test()
Dim OutApp As Object
Dim OutMail As Object
Dim cell As Range

Application.ScreenUpdating = False
Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon

On Error GoTo cleanup
For Each cell In Sheets("Sheet1").Columns("B").Cells.SpecialCells
(xlCellTypeConstants)
If cell.Value Like "?*@?*.?*" And LCase(cell.Offset(0, 1).Value) =
"yes" _
And LCase(cell.Offset(0, 2).Value) <> "sent" Then
Set OutMail = OutApp.CreateItem(0)

On Error Resume Next
With OutMail
.to = cell.Value
.Subject = "Welcome to Company"
.Body = "Dear " & cell.Offset(0, -1).Value & vbNewLine &
vbNewLine & _
"We are excited that you are joinging our team, and
felt that your qualifications might be a great fit for a position that I
currently have available. Based upon the information we have regarding your
compensation and benefit needs, I am confident that we have a position that
meets or exceeds your expectations." & vbNewLine & vbNewLine & _
"I would like to invite you to schedule yourself to
discuss the opportunity further. The link below will allow you to schedule
yourself during a time that is suitable for you. When you begin to enter your
contact information, please reference the Taleo # 9999999. Please expect a
call at the time that you choose with the primary phone number you provide."
& vbNewLine & vbNewLine & _
"https://website" & vbNewLine & vbNewLine & _
"I invite you to learn more about us at
www.company.com, and look forward to connecting with you soon." & vbNewLine &
vbNewLine & _
"Thank You," & vbNewLine & vbNewLine & _
"Hiring Manager" & vbNewLine & _
"Regional Recruiter"
.Importance = 2 'High importance
.ReadReceiptRequested = True

'You can add files also like this
'.Attachments.Add ("C:\test.txt")
.display
Application.Wait (Now + TimeValue("0:00:02"))
Application.SendKeys "%S%g%g"
Application.Wait (Now + TimeValue("0:00:02"))


'Or use Display
End With
On Error GoTo 0

cell.Offset(0, 2).Value = "sent"
Set OutMail = Nothing
End If
Next cell

cleanup:
Set OutApp = Nothing
Application.ScreenUpdating = True
End Sub

Hard to tell, can't see your code. Maybe you can find an answer at this site.

http://www.rondebruin.nl/sendmail.htm

Hello,

[quoted text clipped - 9 lines]

Thank you very much!
Gina

 

[Gina_28 via OfficeKB.com]

Hi Ron,

I got the send feature to work by adding (.Send) in front of (Or Use Display),
but not I'm having an entirely different problem.

You seem to be the expert on these macro mail codes. I have a paragraph that
is inserting information from cells on the worksheet into the paragraphs.
But whenever I try to add the reference to the cell(range).Value, it will not
include anything into my message anymore, and then error out the rest of the
message macro code. Any idea what I'm doing wrong

Try to change this

.display
Application.Wait (Now + TimeValue("0:00:02"))
Application.SendKeys "%S%g%g"
Application.Wait (Now + TimeValue("0:00:02"))

to

.Display
Application.Wait (Now + TimeValue("0:00:02"))
Application.SendKeys "%S"

But Sendkeys is not always working OK
Be sure when you test it you close the VBA editor

Another option is to use Excel/Outlook 2007
There are no security warnings then or use CDO
http://www.rondebruin.nl/cdo.htm

Thank you! I'm at that site right now, searching for just this piece, but
I'm still new to macros so ...kinda searching in the dark. Here is the code

[quoted text clipped - 74 lines]

 

[Ron de Bruin]

Hi Gina

I must see a example to see what is wrong
You can send me a example workbook private and i will look at it

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm

Hi Ron,

I got the send feature to work by adding (.Send) in front of (Or Use Display),
but not I'm having an entirely different problem.

You seem to be the expert on these macro mail codes. I have a paragraph that
is inserting information from cells on the worksheet into the paragraphs.
But whenever I try to add the reference to the cell(range).Value, it will not
include anything into my message anymore, and then error out the rest of the
message macro code. Any idea what I'm doing wrong

Try to change this

.display
Application.Wait (Now + TimeValue("0:00:02"))
Application.SendKeys "%S%g%g"
Application.Wait (Now + TimeValue("0:00:02"))

to

.Display
Application.Wait (Now + TimeValue("0:00:02"))
Application.SendKeys "%S"

But Sendkeys is not always working OK
Be sure when you test it you close the VBA editor

Another option is to use Excel/Outlook 2007
There are no security warnings then or use CDO
http://www.rondebruin.nl/cdo.htm

Thank you! I'm at that site right now, searching for just this piece, but
I'm still new to macros so ...kinda searching in the dark. Here is the code

[quoted text clipped - 74 lines]

Thank you very much!
Gina

 

[Gina_28 via OfficeKB.com]

Hi Ron,

I'm sorry, I don't know how to send the message privately (searched for the
option in here but couldn't find-I'm a newbie). But, here is the body of the
text that I've created so far. When I run the macro, it will open the
message, but the body is empty.

.Body = "Dear " & cell.Offset(0, -3).Value & vbNewLine & vbNewLine & _
"We are pleased to move you forward to the final
stage of the interview process. Below you will find the information needed
to attend your interview. You will be joining a conference call through
MeetingPlace for the audio portion, and a web-based application called DimDim
for the visual part of the presentation. You must login to both portions of
the interview!" & vbNewLine & vbNewLine & _
"We remind you that your interview starts promptly at
" & cell.Offset(0, -8).Value


The cell Offset is referencing a cell that has a date in it with the date
format of hh:mm AM/PM

when I take the cell.Offset off of the line, it runs fine and pulls the
message into the body. But, I need to have it reference that cell.

Hi Gina

I must see a example to see what is wrong
You can send me a example workbook private and i will look at it

Hi Ron,

[quoted text clipped - 32 lines]

 

[Ron de Bruin]

You use

cell.Offset(0, -8).Value

If the Cell column is for example (4 = column D)
This line will blow because column -4 not exist

Tell me in what column your mail addresses are and the date/time

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm

Hi Ron,

I'm sorry, I don't know how to send the message privately (searched for the
option in here but couldn't find-I'm a newbie). But, here is the body of the
text that I've created so far. When I run the macro, it will open the
message, but the body is empty.

.Body = "Dear " & cell.Offset(0, -3).Value & vbNewLine & vbNewLine & _
"We are pleased to move you forward to the final
stage of the interview process. Below you will find the information needed
to attend your interview. You will be joining a conference call through
MeetingPlace for the audio portion, and a web-based application called DimDim
for the visual part of the presentation. You must login to both portions of
the interview!" & vbNewLine & vbNewLine & _
"We remind you that your interview starts promptly at
" & cell.Offset(0, -8).Value


The cell Offset is referencing a cell that has a date in it with the date
format of hh:mm AM/PM

when I take the cell.Offset off of the line, it runs fine and pulls the
message into the body. But, I need to have it reference that cell.

Hi Gina

I must see a example to see what is wrong
You can send me a example workbook private and i will look at it

Hi Ron,

[quoted text clipped - 32 lines]

Thank you very much!
Gina

 

[Gina_28 via OfficeKB.com]

email address are in column J and the time is in column B

You use

cell.Offset(0, -8).Value

If the Cell column is for example (4 = column D)
This line will blow because column -4 not exist

Tell me in what column your mail addresses are and the date/time

Hi Ron,

[quoted text clipped - 29 lines]

 

[Ron de Bruin]

Send me the test workbook to my private mail
You can find it on my site
http://www.rondebruin.nl/



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm

email address are in column J and the time is in column B

You use

cell.Offset(0, -8).Value

If the Cell column is for example (4 = column D)
This line will blow because column -4 not exist

Tell me in what column your mail addresses are and the date/time

Hi Ron,

[quoted text clipped - 29 lines]

Thank you very much!
Gina

 

[Ron de Bruin]

First you can use format to display your time

Format(cell.Offset(0, -8), "h:mm")

Then remove this at the end of the body string

& _




--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm

Send me the test workbook to my private mail
You can find it on my site
http://www.rondebruin.nl/



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm

email address are in column J and the time is in column B

You use

cell.Offset(0, -8).Value

If the Cell column is for example (4 = column D)
This line will blow because column -4 not exist

Tell me in what column your mail addresses are and the date/time

Hi Ron,

[quoted text clipped - 29 lines]
Thank you very much!
Gina

 

[Gina_28 via OfficeKB.com]

Ok, I added that, but then it returns only a '0' in the message body (leaving
the & _) still attached. So, I removed the (& _) as you said, and now it
won't run, giving me a syntax error on the rest of the message body below
this line.

First you can use format to display your time

Format(cell.Offset(0, -8), "h:mm")

Then remove this at the end of the body string

& _

Send me the test workbook to my private mail
You can find it on my site

[quoted text clipped - 16 lines]

 

[Ron de Bruin]

Must look like this

"-Pacific: Subtract 1 hour " & vbNewLine & vbNewLine & vbNewLine
.Importance = 2 'High importance
.ReadReceiptRequested = True


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm

Ok, I added that, but then it returns only a '0' in the message body (leaving
the & _) still attached. So, I removed the (& _) as you said, and now it
won't run, giving me a syntax error on the rest of the message body below
this line.

First you can use format to display your time

Format(cell.Offset(0, -8), "h:mm")

Then remove this at the end of the body string

& _

Send me the test workbook to my private mail
You can find it on my site

[quoted text clipped - 16 lines]

Thank you very much!
Gina

 

[Ron de Bruin]

I have send you a example file

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm

Must look like this

"-Pacific: Subtract 1 hour " & vbNewLine & vbNewLine & vbNewLine
.Importance = 2 'High importance
.ReadReceiptRequested = True


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm

Ok, I added that, but then it returns only a '0' in the message body (leaving
the & _) still attached. So, I removed the (& _) as you said, and now it
won't run, giving me a syntax error on the rest of the message body below
this line.

First you can use format to display your time

Format(cell.Offset(0, -8), "h:mm")

Then remove this at the end of the body string

& _

Send me the test workbook to my private mail
You can find it on my site
[quoted text clipped - 16 lines]
Thank you very much!
Gina

 

[Gina_28 via OfficeKB.com]

Ron, you are a genius!! PROBLEM SOLVED!! Thank you very very much!!

I have send you a example file

Must look like this

[quoted text clipped - 20 lines]