PC Review


Reply
Thread Tools Rating: Thread Rating: 1 votes, 5.00 average.

Pass Value from Textbox on Form to VBA Code

 
 
ryguy7272
Guest
Posts: n/a
 
      2nd Jan 2009
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---



--
RyGuy
 
Reply With Quote
 
 
 
 
ryguy7272
Guest
Posts: n/a
 
      3rd Jan 2009
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--

--
RyGuy


"Chris O'C via AccessMonster.com" wrote:

> Use this:
>
> Set MyRS = MyDB.OpenRecordset(Me.textboxname)
>
> Chris
> Microsoft MVP
>
>
> ryguy7272 wrote:
>
> >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?

>
> --
> Message posted via http://www.accessmonster.com
>
>

 
Reply With Quote
 
 
 
 
ryguy7272
Guest
Posts: n/a
 
      3rd Jan 2009
Makes perfect sense!! Thanks again Chris!!!
Ryan---


--
RyGuy


"Chris O'C via AccessMonster.com" wrote:

> 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 wrote:
> >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?

>
> --
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/For...dules/200901/1
>
>

 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
VBA IIf(myCell.Value = Array(myCell2(1).Value, myCell2(2).Value,myCell2(3).Value,myCell2(4).Value) ,"OK","No Match) EagleOne@discussions.microsoft.com Microsoft Excel Programming 2 24th Apr 2009 11:25 PM
Calculate Textbox value based on another textbox value.doc Tdungate Microsoft Excel Misc 1 12th Feb 2009 08:11 PM
Calculate Textbox value based on another textbox value Tdungate Microsoft Excel Misc 0 12th Feb 2009 08:03 PM
how pass value from a textbox in form to report? jaYPee Microsoft Access ADP SQL Server 3 23rd Mar 2005 05:25 PM
Is it possible to open the VBA form with a link in a sheet and to pass variable from a cell to the VBA form? Daniel Microsoft Excel Programming 1 29th Aug 2004 01:20 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 12:24 PM.