MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Controlling Outlook Express with VBA/Excel

  Asked By: Kelley    Date: Jan 27    Category: MS Office    Views: 3683

For a while now I've been using VBA in Excel to control Groupwise,
using it to send multiple attachments to multiple lists of
recipients. This has been working great, but now I need to replicate
the same process with Outlook Express (don't ask why, the answer to
that is just too boring).

I've got so far with this. First off, it seems that Outlook Express
doesn't lend itself too well to this sort of thing. After rooting
around on the web I've found various bits of code using the MAPI
function MAPISendMail. I've got one bit of code that can send to
multiple recipients, but can't handle attachments. I've got another
bit that can send one attachment to one recipient. The difficulty
I'm having is marrying them together. However, I approach this it
always seems to bomb out at the same place - telling the MAPIMessage
the address of the start of the recipients array. Excel just dies at
this point. I can't understand why, especially considering that the
same code works fine regarding the recipients array.

Has anyone got any thoughts on this, or can anyone direct me to some
code that actually works?



6 Answers Found

Answer #1    Answered By: Alarice Miller     Answered On: Jan 27

If someone is not able to help you here, you may want to try asking your
question in the Advanced Outlook group. Granted that is focused more on
Outlook, but it is possible that someone can point you to a code  sample
which works using  OE.

Answer #2    Answered By: Frederik Jones     Answered On: Jan 27

Might be helpful if you post some code; then we can step through it with
you and see why  it bombs.

Answer #3    Answered By: Niran Jainukul     Answered On: Jan 27

Bad News is that Outlook Express is not programmable.

Answer #4    Answered By: Femke Bakker     Answered On: Jan 27

There is a code  zip file at the bottom of this page.....
It should go without saying, be careful, scan for viruses and don't
enable macros until you've read through the code.

Answer #5    Answered By: Adalhelm Fischer     Answered On: Jan 27

Unfortunately though it only seems to send  one
attachment to one recipient. I'm OK with that bit. The challenge
seems to be sending multiple  attachments to multiple recipients.

As someone else suggested, I'm adding some code.....

Sub SendMessage()
Dim rc As Long
Dim msg As MapiMessage
Dim recip(1) As MapiRecip
Dim attach(1) As MapiFile

'recip(1).Name = StrConv("pjs", vbFromUnicode)
recip(1).Address = StrConv("smtp:a.name@...", vbFromUnicode)
recip(1).RecipClass = 1
attach(1).Position = -1
attach(1).PathName = StrConv("c:\testfile.txt", vbFromUnicode)
msg.Subject = "test subject"
msg.NoteText = "this one should have an attachment"
msg.RecipCount = 1
msg.Recipients = VarPtr(recip(1))
msg.FileCount = 1
msg.Files = VarPtr(attach(1))

rc = MAPISendMail(0, 0, msg, 0, 0)

End Sub

This works  fine (as long as you've already defined the MAPI function
MAPISendMail and got hold of an appropriate dll file). The fun
starts when you want to send multiple attachments to multiple
recipients. As you can see the attachments and recipients  are
arrays, so it 'should' be straightforward. The difficulty seems to
be that, in the code  I've seen so far, you have to use either the
VarPtr or VarPtrArray function to tell the MAPISendMail the address
of the first element in the array. The following routine uses this

Sub SendMailWithOE(ByVal strSubject As String, _
ByVal strMessage As String, _
ByRef aRecips As Variant, _
Optional ByVal vfiles As String)

Dim aFiles() As String
Dim recips() As MAPIRecip
Dim filepaths() As MAPIFile
Dim attachments() As MAPIFile
Dim message As MAPIMessage
Dim z As Long
ReDim recips(LBound(aRecips) To UBound(aRecips))
ReDim attachments(LBound(aRecips) To UBound(aRecips))

'This bit  finds
For z = LBound(aRecips) To UBound(aRecips)
With recips(z)
.RecipClass = 1
If InStr(aRecips(z), "@") <> 0 Then
.Address = StrConv(aRecips(z), vbFromUnicode)
.Name = StrConv(aRecips(z), vbFromUnicode)
End If
End With
Next z

'This next bit does the same as above but for the attachments -
I've got from another routine
aFiles = Split(vfiles, ",")
ReDim filepaths(LBound(aFiles) To UBound(aFiles))
For z = LBound(aFiles) To UBound(aFiles)
With filepaths(z)
.Position = -1
.PathName = StrConv(aFiles(z), vbFromUnicode)
End With
Next z

With message
.NoteText = strMessage
.Subject = strSubject
.RecipCount = UBound(recips) + 1
.Recipients = VarPtr(recips(LBound(recips)))
.FileCount = UBound(filepaths) - LBound(filepaths) + 1 'new
.Files = VarPtr(filepaths(LBound(filepaths))) 'new

End With
MAPISendMail 0, 0, message, 0, 0

End Sub

This works for the recipients. I took the same idea (and some code
from elsewhere) to replicate it for attachments, but the line

.Files = VarPtr(filepaths(LBound(filepaths)))

makes Excel crash. Presumably its not supplying the right address,
but I've no idea why.....

Answer #6    Answered By: Tyreece Thompson     Answered On: Jan 27

I don't use Outlook Express, but I do loop through my recordset and
gather multiple  email addresses to be used.

Dim strCC as String

Do While Not rst.EOF
strCC = strCC & rst![eMail] & "; "

You should be able to use strCC as your recipient list.

Just another thought.

Didn't find what you were looking for? Find more on Controlling Outlook Express with VBA/Excel Or get search suggestion and latest updates.