accessing mail through vba

[Dudely]

I'm trying to mail something to a list of addresses in my
spreadsheet. As best as I can tell, everything works fine EXCEPT, the
mail never seems to go out. I don't believe there are any errors
being returned as I step through it and it seems to execute each
statement just fine. Any ideas? Here's the function. I have Office
2000 installed on WinXP.


Sub mailMessage()
Dim OutApp As Object
Dim OutMail As Object
Dim cell As Range
Dim strBody As String



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

Call getBody(strBody)

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

On Error Resume Next
With OutMail
.To = cell.Value
.Subject = "Enter Subject Here"
.Body = strBody
.Send
End With
On Error GoTo 0

Set OutMail = Nothing
End If
Next cell

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

 

[Doug Robbins - Word MVP]

You probably need to make use of the Express ClickYes utility that is
mentioned in the Preparations section of the article "Mail Merge to E-mail
with Attachments" at:

http://word.mvps.org/FAQs/MailMerge/MergeWithAttachments.htm

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

 

[Dudely]

I suppose it's possible I might need to make use of that utitlity,
however at the moment that answer does not address the problem I'm
actually having as best as I can tell.

Anybody else know what's going on here? Is there a more appropriate
group to ask this question? The outlook people didn't seem all that
knowledgable in general and I don't think this is the kind of question
they would know much about since it's about programming and not the
application itself necessarily.

Thank you

 

[Doug Robbins - Word MVP]

Your code appears to be essentially the same as that in the article to which
I referred you. Are you able to send a document via email by using the
File>Send facility in Word? If not, see the following page of fellow MVP
Peter Jamieson's website:

http://tips.pjmsn.me.uk/t0002.htm

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

I suppose it's possible I might need to make use of that utitlity,
however at the moment that answer does not address the problem I'm
actually having as best as I can tell.

Anybody else know what's going on here? Is there a more appropriate
group to ask this question? The outlook people didn't seem all that
knowledgable in general and I don't think this is the kind of question
they would know much about since it's about programming and not the
application itself necessarily.

Thank you

 

[Dudely]

Actually the code comes from here: http://www.rondebruin.nl/mail/folder3/message.htm

I made some fairly minor modifications.

And yes, I can email from Word using File>Send.

So, the problem still remains. Is there perhaps a temp directory I
can check to see if the mail is just sitting there for some reason?

Thank you

 

[Doug Robbins - Word MVP]

Have you looked in the Outbox

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

Actually the code comes from here:
http://www.rondebruin.nl/mail/folder3/message.htm

I made some fairly minor modifications.

And yes, I can email from Word using File>Send.

So, the problem still remains. Is there perhaps a temp directory I
can check to see if the mail is just sitting there for some reason?

Thank you

 

[Dudely]

There is nothing in the outbox. And, just so you know I'm only
attempting to send out one message - to myself - so that I know
whether or not it's sent & received.

 

[Ron de Bruin]

Hi Dudely

Change

..Send

To

..Display

Do you see the mails ?

Are the mail addresses manual entered in Column A?

--

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


There is nothing in the outbox. And, just so you know I'm only
attempting to send out one message - to myself - so that I know
whether or not it's sent & received.

 

[Dudely]

Hi Ron!

Changing to .Display brings up an Outlook mail pre-addressed to myself
with the correct subject. Interestingly, there is no body.

One thing I did was change the body to include rows through 30. I
also notice that the body var (strBody) contains all non-printables
for some reason.

In any event, I now SEND the mail by clicking the send button manually
and it seems to send in that I don't get any errors of any kind...
but, I never get the email back; i.e. it's not delivered to me. So I
assume it's not going out.

Sending an email to myself through Outlook the normal way generates a
return email exactly as expected.

(And yes, email is in column A and the "yes" is in column C)

 

[Dudely]

I fixed the body problem. I was still looking in column E, whereas
the message was in column A. Silly me. The unprintable chars turned
out to be the newlines being added. So, that's not an issue any
more. Just the original stated problem remains. Can't send email.

Thank you

 

[Dudely]

I seem to have found the problem. I was filtering the incoming email
and deleting it automatically before I ever got a chance to see it. I
figured it out by changing my email address and sending it to gmail
instead of back to myself with Outlook. So the problem never actually
existed, but without being able to see the email in the first place
using .Display, it would of taken me much longer to figure out what I
was doing wrong. So thanks very much for your assistance Ron, and of
course thank you for the original code as well! Thanks also to Doug
for your kind efforts.

 

[Graham Mayor]

It might help to set Outlook not to send mail immediately - then you don't
get any embarrassing faux pas when testing with your live data. The mail
then goes to your outbox where it remains until you send it.

--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>