Desperately seeking a solution to generating emails from Excel.



Hi all,

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

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,

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
End With

End If
Next olMail

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

End Sub



Sue Mosher [MVP]

Probably because orig_from is not an email address but the name of a person whose address Outlook can't determine. There is no Outlook property that returns the sender's email address. You can either use CDO (or Redemption to avoid security prompts -- to get the From address or use Outlook to get the Reply To address. Sample code at

Sue Mosher, Outlook MVP
Outlook and Exchange solutions at
Author of
Microsoft Outlook Programming: Jumpstart
for Administrators, Power Users, and Developers


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question