Pass Value from Textbox on Form to VBA Code

[ryguy7272]

I am using the code below to send emails:
Sub SendMessages(Optional AttachmentPath)

Dim MyDB As DAO.Database
Dim MyRS As DAO.Recordset
Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Outlook.Recipient
Dim objOutlookAttach As Outlook.Attachment
Dim TheAddress As String

Set MyDB = CurrentDb
Set MyRS = MyDB.OpenRecordset("tblMailingList")
MyRS.MoveFirst

' Create the Outlook session.
Set objOutlook = CreateObject("Outlook.Application")

Do Until MyRS.EOF
' Create the e-mail message.
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
TheAddress = MyRS![EmailAddress]

With objOutlookMsg
' Add the To recipients to the e-mail message.
Set objOutlookRecip = .Recipients.Add(TheAddress)
objOutlookRecip.Type = olTo

' Add the Cc recipients to the e-mail message.
If (IsNull(Forms!frmMail!CCAddress)) Then
Else
Set objOutlookRecip = .Recipients.Add(Forms!frmMail!CCAddress)
objOutlookRecip.Type = olCC
End If

' Set the Subject, the Body, and the Importance of the e-mail message.
..Subject = Forms!frmMail!Subject
..Body = Forms!frmMail!MainText
..Importance = olImportanceHigh 'High importance

' Add attachments to the message.
With Application.FileSearch
.LookIn = Forms!frmMail!Att
.FileName = "*.*"
.Execute

For i = 1 To .FoundFiles.Count
objOutlookMsg.Attachments.Add .FoundFiles(i)
Next i
End With


' Resolve the name of each Recipient.
For Each objOutlookRecip In .Recipients
objOutlookRecip.Resolve
If Not objOutlookRecip.Resolve Then
objOutlookMsg.Display
End If
Next
..Send
End With
MyRS.MoveNext
Loop
Set objOutlookMsg = Nothing
Set objOutlook = Nothing
End Sub

The code works great. I am just trying to modify it a bit so I can choose
different sources, such as different queries, for my emails. Right now, all
emails are stored in my ‘tblMailingList’. I’d like to create queries based
on this table and then use these queries as my source for sending emails. I
would surmise that the below line of code has to change:

Set MyRS = MyDB.OpenRecordset("tblMailingList")

What I’d like to do is enter the name of a query, in a textbox on a form,
and change it here rather than go into the VBA and change it there. So,
somehow I’d have to pass the value from the textbox to some kind of variable,
in the example above. How do I do this?

Thanks,
Ryan---

 

[Chris O'C via AccessMonster.com]

Use this:

Set MyRS = MyDB.OpenRecordset(Me.textboxname)

Chris
Microsoft MVP

 

[ryguy7272]

Thank you very much Chris! This is pretty much what I was looking for. I
actually had an error at first, and realized that the "ME" keyword only works
for the "code behind forms" modules. I moved my code to the Form and it
worked great. Before this, the code was in a Module. For edification
purposes, how would I have to change the code to get the Form to reference
the code, and all related controls, in a Module?

Thanks again,
Ryan--

 

[Chris O'C via AccessMonster.com]

To reference a textbox control on an open form from another form or a module,
use this syntax:

Set MyRS = MyDB.OpenRecordset(Forms!formname.textboxname)

Chris

 

[ryguy7272]

Makes perfect sense!! Thanks again Chris!!!
Ryan---