VBScript for custom form

[tguenther]

Hi,

I'm not familiar with VBScript and need some help with the Item_Send
event in the code behind a custom mail message form.

I want to cycle through an access table and send an email to each
contact in the record. I have created the code I want to execute in an
Access database but of course not sending the email. Here is my code:
I need to write it in VBScript

Dim DB As DAO.database
Dim RS As DAO.Recordset
Dim SQ As String
Dim lcDatbase As String

Set DB = DAO.OpenDatabase("C:\Documents and Settings\t\My
Documents\eml") ' or whatever database you want using the path to it

SQ = "Select * from eml"

Set RS = DB.OpenRecordset(SQ, dbOpenDynaset)

While Not RS.EOF

Dim lcConEmail As String
Dim lcName As String

If Not IsNull(RS!) Then
lcConEmail = RS![email]
Else
lcConEmail = ""
End If

If Not IsNull(RS![contact]) Then
lcName = RS![contact]
Else
lcName = ""
End If

'Don't send message if no email address
If lcConEmail <> "" And lcName <> "" Then


Set mOApp = New Outlook.Application
Set mONs = mOApp.GetNamespace("MAPI")
Set mOMailItem = mOApp.CreateItemFromTemplate("c:\Documents and
Settings\t\My Documents\test.oft")
With mOMailItem

.To = lcConEmail

lcName = Replace(lcName, Chr(34), "")
lcBody = "Dear {Recipient}"

NewBody = Replace(lcBody, "{Recipient}", lcName)

.Body = NewBody


If .Recipients.ResolveAll Then
.Send
End If
End With

Else
lnOk = MsgBox("No contact or email to create mail message",
vbOKOnly, "Message")
End If


RS.MoveNext
Wend

 

[Hollis D. Paul]

Dim DB As DAO.database
Dim RS As DAO.Recordset
Dim SQ As String
Dim lcDatbase As String

Just for starters every variable in VBScript is a variant, so the As-
clause in Dim statements will cause an error.

You just have to try something and see if the "compiler" accepts it.
It isn't shy, and doesn't get tired, so it will tell you every time
when it doesn't like something that VB will take.

Hollis D. Paul [MVP - Outlook]
(e-mail address removed)
Using Virtual Access 4.52 build 277 (32-bit), Windows 2000 build 2195
http://search.support.microsoft.com/kb/c.asp?FR=0&SD=TECH&LN=EN-US

Mukilteo, WA USA

 

[tguenther]

I'm getting there. Do you know how to use the replace() function in vbscript?

Dim DB As DAO.database
Dim RS As DAO.Recordset
Dim SQ As String
Dim lcDatbase As String

Just for starters every variable in VBScript is a variant, so the As-
clause in Dim statements will cause an error.

You just have to try something and see if the "compiler" accepts it.
It isn't shy, and doesn't get tired, so it will tell you every time
when it doesn't like something that VB will take.

Hollis D. Paul [MVP - Outlook]
(e-mail address removed)
Using Virtual Access 4.52 build 277 (32-bit), Windows 2000 build 2195
http://search.support.microsoft.com/kb/c.asp?FR=0&SD=TECH&LN=EN-US

Mukilteo, WA USA

 

[Hollis D. Paul]

I'm getting there. Do you know how to use the replace() function in vbscript?

The number 1 resource for things Outlook and Exchange is www.slipstick.com. I
did a search there using replace and the following result may be helpful:

How to search and replace field values in Microsoft Outlook
URL: http://www.slipstick.com/outlook/searchreplacecompany.htm - 12KB - 21 Jan
2003

Hollis D. Paul [MVP - Outlook]
(e-mail address removed)
Using Virtual Access 4.52 build 277 (32-bit), Windows 2000 build 2195
http://search.support.microsoft.com/kb/c.asp?FR=0&SD=TECH&LN=EN-US

Mukilteo, WA USA

 

[tguenther]

Ok,

I'm starting to understand what to do and what not to do in VBScript.
I got the Replace() Function working. One thing I can't figure out
with this custom mail message form is how to make it not check the
name of what is in the To: textbox when I first click on Send. I'm
populating it with a value for display purposes. I don't want to send
a message to the value in there or have it check against the address
book. How do I do this?

Thank you again.

I'm getting there. Do you know how to use the replace() function in vbscript?

Dim DB As DAO.database
Dim RS As DAO.Recordset
Dim SQ As String
Dim lcDatbase As String

Just for starters every variable in VBScript is a variant, so the As-
clause in Dim statements will cause an error.

You just have to try something and see if the "compiler" accepts it.
It isn't shy, and doesn't get tired, so it will tell you every time
when it doesn't like something that VB will take.

Hollis D. Paul [MVP - Outlook]
(e-mail address removed)
Using Virtual Access 4.52 build 277 (32-bit), Windows 2000 build 2195
http://search.support.microsoft.com/kb/c.asp?FR=0&SD=TECH&LN=EN-US

Mukilteo, WA USA

 

[Hollis D. Paul]

One thing I can't figure out
with this custom mail message form is how to make it not check the
name of what is in the To: textbox when I first click on Send. I'm
populating it with a value for display purposes. I don't want to send
a message to the value in there or have it check against the address
book. How do I do this?

That To: textbox isn't a standard textbox. It will always be processed
to set up the value as an Outlook Recipient object. You have to use one
of the standard email address formats that 1) contains both the display
name and the email address and 2) Outlook recognizes and transforms
according to your preference. The alternative is to generate a
Recipient Object using the Outlook Object Model, and stuff it with the
strings you have in mind, plus the proper type value, and add it to the
message's Recipient Object Collection set. Finally, I think you have
to do a resolve All method to get it accepted. Might be wrong on that.

Hollis D. Paul [MVP - Outlook]
(e-mail address removed)
Using Virtual Access 4.52 build 277 (32-bit), Windows 2000 build 2195
http://search.support.microsoft.com/kb/c.asp?FR=0&SD=TECH&LN=EN-US

Mukilteo, WA USA

 

[tguenther]

Thanks for the info. I have found that if I put in a email address and
click send it doesn't do the name check. Problem is I need to put in a
string not an email address into the To: textbox. Is there any way to
bypass the checking of the name and the initial send? I tried to
ResolveAll but it doesn't work. I don't want to fire the default code
in send button. I want to use my custom code which will send an email
to records in an Access table. I think I might have to create a custom
send button to do this. What do you think?

One thing I can't figure out
with this custom mail message form is how to make it not check the
name of what is in the To: textbox when I first click on Send. I'm
populating it with a value for display purposes. I don't want to send
a message to the value in there or have it check against the address
book. How do I do this?

That To: textbox isn't a standard textbox. It will always be processed
to set up the value as an Outlook Recipient object. You have to use one
of the standard email address formats that 1) contains both the display
name and the email address and 2) Outlook recognizes and transforms
according to your preference. The alternative is to generate a
Recipient Object using the Outlook Object Model, and stuff it with the
strings you have in mind, plus the proper type value, and add it to the
message's Recipient Object Collection set. Finally, I think you have
to do a resolve All method to get it accepted. Might be wrong on that.

Hollis D. Paul [MVP - Outlook]
(e-mail address removed)
Using Virtual Access 4.52 build 277 (32-bit), Windows 2000 build 2195
http://search.support.microsoft.com/kb/c.asp?FR=0&SD=TECH&LN=EN-US

Mukilteo, WA USA

 

[tguenther]

I found a way to do what I wanted. Don't know if it is the best way of
doing it. I created a button on the custom mail message form. I
cancelled the Item_BeforeCheckNames method and after that called the
code in my Command button which takes the body and subject of the mail
message and creates an email for each record in the table. I also
can't open the form from my database, I can only get this to work when
running the form from Outlook. Any ideas how to open the form from
another application?

One thing I can't figure out
with this custom mail message form is how to make it not check the
name of what is in the To: textbox when I first click on Send. I'm
populating it with a value for display purposes. I don't want to send
a message to the value in there or have it check against the address
book. How do I do this?

That To: textbox isn't a standard textbox. It will always be processed
to set up the value as an Outlook Recipient object. You have to use one
of the standard email address formats that 1) contains both the display
name and the email address and 2) Outlook recognizes and transforms
according to your preference. The alternative is to generate a
Recipient Object using the Outlook Object Model, and stuff it with the
strings you have in mind, plus the proper type value, and add it to the
message's Recipient Object Collection set. Finally, I think you have
to do a resolve All method to get it accepted. Might be wrong on that.

Hollis D. Paul [MVP - Outlook]
(e-mail address removed)
Using Virtual Access 4.52 build 277 (32-bit), Windows 2000 build 2195
http://search.support.microsoft.com/kb/c.asp?FR=0&SD=TECH&LN=EN-US

Mukilteo, WA USA

 

[tguenther]

Thanks for all your help on this. I opened my form with the following code:

loFolder = loNameSpace.GetDefaultFolder(6)
loMail = loFolder.Items.Add("IPM.Note.MYMessage")

Any ideas how to open the form from
another application?

You have to use the GetObject function from your application to open an
instance of Outlook, and then use the Outlook Object Model from there.

Hollis D. Paul [MVP - Outlook]
(e-mail address removed)
Using Virtual Access 4.52 build 277 (32-bit), Windows 2000 build 2195
http://search.support.microsoft.com/kb/c.asp?FR=0&SD=TECH&LN=EN-US

Mukilteo, WA USA

 

[Hollis D. Paul]

<You have to use the GetObject function from your application to open an
instance of Outlook, and then use the Outlook Object Model from there.>

Thanks for all your help on this. I opened my form with the following code:

loFolder = loNameSpace.GetDefaultFolder(6)
loMail = loFolder.Items.Add("IPM.Note.MYMessage")

I take it that loFolder is an object in the Access object model that provides
the interopation between Access and Outlook. As I said before, you need to
use the objects in the Outlook Object Model.

You need to set a reference to the Outlook Type Library in your project then
do the following:

Set OutLookApp = GetObject("Outlook.Application", <profile info here?)

From there you can use:

Set objNameSpace = OutLookApp.NameSpace

or something like that to get the namespace and then the folder.

Go to www.slipstick.com to get examples of code.

Hollis D. Paul [MVP - Outlook]
(e-mail address removed)
Using Virtual Access 4.52 build 277 (32-bit), Windows 2000 build 2195
http://search.support.microsoft.com/kb/c.asp?FR=0&SD=TECH&LN=EN-US

Mukilteo, WA USA