Sending emails via Excel - please help.

[aah-boy]

Hi all,

Please help, I'm getting desperate - and my scalp is starting to
reflect light !

I've posted numerous emails into the Outlook ng - but I get no
replies.

I have this piece of code which scans my 'sent' folder and is supposed
to generate a reply if any sent emails contain a certain word.

My problem is this: if I try to change the .To property to equal the
original .From property, I get an error on the .Send

The error is as follows:

'Outlook does not recognise one or more names' - on the line of code
where the .Send is.

So to sum-up if I change this:

.To = "(e-mail address removed)"

- to this...

.To = orig_from

- the whole thing fails !

Can someone please try to explain why this occurs?

My code is below. It is fairly generic and should be easily pasteable
and testable into any workbook.

Thanks in anticipation,
Dave




Sub Scan_And_Send_Emails()

Dim OutApp As Outlook.Application
Dim OutMail As Outlook.MailItem
Dim olMail As Outlook.MailItem
Dim olNs As NameSpace
Dim Fldr As MAPIFolder

Dim orig_from As String
Dim orig_subject As String

Dim i As Integer
Dim body_msg As String

Set OutApp = CreateObject("Outlook.Application")
Set olNs = OutApp.GetNamespace("MAPI")
Set Fldr = olNs.GetDefaultFolder(olFolderSentMail)

body_msg = "Testing"

For Each olMail In Fldr.Items

If InStr(olMail.Body, "UK") > 0 Or _
InStr(olMail.Subject, "UK") > 0 Then

' Get the original contents of the email
With olMail
orig_from = .SenderName
orig_subject = .Subject
End With

Set OutMail = OutApp.CreateItem(olMailItem)

' Format our new email
With OutMail
.SentOnBehalfOfName = "the centre"
.To = "(e-mail address removed)"
.Subject = orig_subject
.Body = body_msg
.Send
End With

End If
Next olMail

Set OutMail = Nothing
Set OutApp = Nothing
Set olNs = Nothing
Set Fldr = Nothing

End Sub

 

[jaf]

Hi Dave,
I'm not real familiar.
Try this as a test.

' Get the original contents of the email
With olMail
orig_from = .SenderName
orig_subject = .Subject
End With

Debug.Print orig_from,orig_subject '<<<<<<<<<<<<


What prints to the immediate window.
Does it include quotes?
Does it need quotes?

 

[aah-boy]

Hi Dave,

Hi Jal,

I'm not real familiar.
Try this as a test.

' Get the original contents of the email
With olMail
orig_from = .SenderName
orig_subject = .Subject
End With

Debug.Print orig_from,orig_subject '<<<<<<<<<<<<


What prints to the immediate window.

I do not get any window appearing at all.

Should I do ?

Does it include quotes?
Does it need quotes?

Regards,
Dave

 

[jaf]

Hi Dave,
Not a window, the "immediate window". View>immediate window or <ctl> G in
the VBA editor.

 

[Dick Kusleika]

Dave

It worked OK for me, but try this: Change

Set OutMail = OutApp.CreateItem(olMailItem)

to

Set OutMail = olMail.Reply

Then don't set the To property. You also may not want to set the Subject
property. The reply will but an RE: in front of the subject. If want that,
don't change Subject.

 

[aah-boy]

On Tue, 4 Nov 2003 15:17:13 -0600, "Dick Kusleika"

Thanks Dick and thanks too to Jaf.

I think I sorted this out now. I didn't have Outlook set up properly -
which I'm assuming (dangerous I know) is the reason it kept objecting
on the .Send line.

- I'm an old stalwart and Agent email user.

My emails now work ok.

I don't know what's wrong with the Outlook ng - but none of my
questions have yet received answers.

Thumbs up to this ng then !

Thanks again to all the helpful people who make this ng great,
Dave

 

[fats12]

Hey Dave,

Did you ever solve this problem??? I have exactly the same one!

If you did please could you mail me the solution at-:

christian.hatton at hp.com

As I am also losing hair!

Thanks!

Chris.