Pass Value from Textbox on Form to VBA Code

Discussion in 'Microsoft Access VBA Modules' started by ryguy7272, Jan 2, 2009.

  1. ryguy7272

    ryguy7272 Guest

    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
     
    ryguy7272, Jan 2, 2009
    #1
    1. Advertisements

  2. ryguy7272

    ryguy7272 Guest

    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
    >
    >
     
    ryguy7272, Jan 3, 2009
    #2
    1. Advertisements

  3. ryguy7272

    ryguy7272 Guest

    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/Forums.aspx/access-modules/200901/1
    >
    >
     
    ryguy7272, Jan 3, 2009
    #3
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Rusty Shackelford

    How to pass a value from one form to another?

    Rusty Shackelford, Feb 27, 2004, in forum: Microsoft Access VBA Modules
    Replies:
    1
    Views:
    163
    Naresh Nichani MVP
    Feb 28, 2004
  2. Guest

    Incorporte Query into VBA or Pass VBA Value to Query

    Guest, Nov 11, 2004, in forum: Microsoft Access VBA Modules
    Replies:
    5
    Views:
    285
    Guest
    Nov 12, 2004
  3. Guest

    Pass-thru vs non-pass thru query in DAO

    Guest, Sep 21, 2006, in forum: Microsoft Access VBA Modules
    Replies:
    4
    Views:
    294
  4. Guest

    Passing value from unbound textbox to bound textbox

    Guest, Feb 15, 2007, in forum: Microsoft Access VBA Modules
    Replies:
    2
    Views:
    1,866
    Guest
    Feb 15, 2007
  5. Guest

    How do I pass a value from one form to another?

    Guest, Jul 26, 2007, in forum: Microsoft Access VBA Modules
    Replies:
    2
    Views:
    184
    Steve
    Jul 26, 2007
Loading...

Share This Page