Using Modules

  • Thread starter Thread starter Ed Harmon via AccessMonster.com
  • Start date Start date
E

Ed Harmon via AccessMonster.com

This seems pretty easy, but I am struggling with it. I have created a
module ("Module 3" I don't know if that is what I should have done) and it
works fine when I run it within the Microsoft Visual Basic window by
pushing F5. My question is how do I use it on a form? You know, click on
a button and have this code execute? I may have done this all wrong, I'm
not sure.

The code takes a table in Access and emails each record to each receiptent,
in an email format using Outlook.

Thanks in advance
 
Make a button using the wizard to do something like open a form...

then go into the OnClick event of the button - get into the code and
replace the two line that open the form with

Call Module 3 ()

And it will run that code
 
Thanks Steve, but I still did something wrong because now it says,

Compile Error
Expected variable or procedure, not module.

Any ideas what I did wrong?

Thanks
 
Is your Module:

Public Sub Model 3()

or listed as something else?
 
Actually the code is small so here is what I have:

Option Compare Database

Option Explicit

Public Declare Function RegisterWindowMessage _
Lib "user32" Alias "RegisterWindowMessageA" _
(ByVal lpstring As String) As Long

Public Declare Function FindWindow Lib "user32" _
Alias "FindWindowA" (ByVal lpClassName As Any, _
ByVal lpWindowName As Any) As Long

Public Declare Function SendMessage _
Lib "user32" Alias "SendMessageA" (ByVal hwnd As Long, _
ByVal wMsg As Long, ByVal vParam As Long, _
lParam As Any) As Long

Sub Sendmessages(Optional AttachmentPath)

Dim mydb As DAO.Database
Dim rs As DAO.Recordset
Dim objOutlook As Outlook.Application
Dim objOutlookmsg As Outlook.MailItem
Dim objOutlookrecip As Outlook.Recipient
Dim theaddress As String
Dim asofdate As String
Dim accountnum As String
Dim parishid As String
Dim parishnm As String
Dim pledgeamt As String
Dim receivedamt As String
Dim balance As String
Dim firstnm As String
Dim lastnm As String

Dim wnd As Long
Dim uclickyes As Long
Dim res As Long

'Register a message to send
uclickyes = RegisterWindowMessage("Clickyes_suspend_resume")

'Find ClickYes Window by classname
wnd = FindWindow("Exclickyes_wnd", 0&)

'Send the message to Resume Clickyes
res = SendMessage(wnd, uclickyes, 1, 0)

Set mydb = CurrentDb
Set rs = mydb.OpenRecordset("statement")
rs.MoveFirst

Set objOutlook = CreateObject("outlook.application")

Do Until rs.EOF

Set objOutlookmsg = objOutlook.CreateItem(olMailItem)
theaddress = rs!
asofdate = rs![Date]
accountnum = rs![parishioner_id#]
parishid = rs![organization_id#_receiver]
parishnm = rs![organization_receiver]
pledgeamt = rs![sumofpledge]
receivedamt = rs![sumofamount]
balance = rs![balance]
firstnm = rs![parishioner_firstnm]
lastnm = rs![parishioner_lastnm]

With objOutlookmsg
Set objOutlookrecip = .Recipients.Add(theaddress)
objOutlookrecip.Type = olTo

.Subject = "Giving update" & " " & Date
.Body = "On behalf of all of the individuals, families and" &
vbCrLf & _
"ministries supported by your donation to the Catholic" &
vbCrLf & _
"Services Appeal, thank you for choosing to give!" & vbCrLf & _
vbCrLf & _
"Your giving record as of" & " " & asofdate & " is:" & vbCrLf &
_
vbCrLf & _
"Account number:" & " " & parishid & "-" & accountnum
& vbCrLf & _
"Parish Name:" & " " & parishnm & vbCrLf & _
vbCrLf & _
"Pledge Amount:" & " " & "$ " & pledgeamt & vbCrLf & _
"Received to date:" & " " & "$ " & receivedamt & vbCrLf &
_
" __________" & vbCrLf & _
"Gift remaining:" & " " & "$ " & balance & vbCrLf & _
vbCrLf & _
"Gift enclosed: ______________" & vbCrLf & _
vbCrLf & _
firstnm & " " & lastnm & ", " & "thank you for your continued
support." & vbCrLf & _
vbCrLf & _
"CSA Campaign" & vbCrLf & _
vbCrLf & _
"Diocese of Gaylord" & vbCrLf & _
"611 West North Street" & vbCrLf & _
"Gaylord, MI 49735" & vbCrLf & _
vbCrLf & _
"(Please print and return a copy of this with your gift, thank
you)"

For Each objOutlookrecip In .Recipients
objOutlookrecip.Resolve
If Not objOutlookrecip.Resolve Then
objOutlookmsg.Display
End If

Next
.Send
End With
rs.MoveNext

Loop
Set objOutlookmsg = Nothing
Set objOutlook = Nothing

'Send the message to Suspend ClickYes
res = SendMessage(wnd, uclickyes, 0, 0)

End Sub

Thanks for your help.
 
Ok to change it to read

Call Sendmessages


That is the Procudure that is using the code to produce the email mails.

If you database currently has REAL records in it you might want to unplug
the network cable so you don't send these emails while your testing the
system.

Also I don't know everything about you database, but I made something
similar one time... How do you know not to send the same email to the same
person twice or three times a day - if someone clicked this button more
than once.

Or even if you did it today then did it tomorrow - do you want to send two
emails back to back...

What I did to answer these and other questions on my emailing system was to
add a datesent field to the underlying table, then a query was the
recordsource that I used and I would have a criteria of today's date - 30
days in the datesent field. That meant that no one would get my emails
within 30 days.

Just some food for thought - Post me back...
 
Thanks Steven,

That did the trick.

Also, thanks for the other tips on controlling the actual emails sent, I
had not considered those factors, I'm kind of new with all this stuff.

Thanks again.

Ed
 
Back
Top