Page 1 of 2 12 LastLast
Results 1 to 15 of 29
  1. #1
    atuljadhav is offline Novice
    Windows 10 Office 365
    Join Date
    Aug 2022
    Posts
    15

    Email address from query table

    Hi expert

    i have two table in access, then make query named "GetData" and getting some data from table1 and from table 2 include customer email id


    below code help to send email to below mentioned email address but it is hard coded, for every customer this email id used to send.
    now what i want the customer email address already exist in "Email_Address" field in above query, so it is automatically take and insert in "To" field and send email.

    i am not expert in VBA but it is possible, please edit my below code
    Query Name = "GetData"
    Email address from = "Email_Address" column.

    Code:
    Sub RTFBodyX()
    Const ForReading = 1, ForWriting = 2, ForAppending = 3
    Dim fs, f
    Dim RTFBody, strTo
    Dim MyItem As Outlook.MailItem
    Dim MyApp As New Outlook.Application
    DoCmd.OutputTo acOutputQuery, "GetData", acFormatHTML, "GetData.htm"
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set f = fs.OpenTextFile("GetData.htm", ForReading)
    RTFBody = f.ReadAll
    'Debug.Print RTFBody
    f.Close
    Set MyItem = MyApp.CreateItem(olMailItem)
    With MyItem
       .To = "atul.jadh@pol.com"
       .Subject = "txtSubjectLine"
       .HTMLBody = RTFBody
    End With
    MyItem.Send
    End Sub

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Do not put quotes around the control...

    .subject = txtBox

  3. #3
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  4. #4
    xps35's Avatar
    xps35 is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Jun 2022
    Location
    Schiedam, NL
    Posts
    229
    Use a recordset to build a loop.

    Code:
    Dim rs as recordset
    |
    |
    Set rs = currentdb.openrecordset("GetData") 
    |
    |
    While not rs.eof
      |
      |
        .to = rs!Email_Address
    
      rs.movenext
    Wend

  5. #5
    atuljadhav is offline Novice
    Windows 10 Office 365
    Join Date
    Aug 2022
    Posts
    15
    Hi,

    Thanks for reply,

    i am getting below error on line " Set rs = CurrentDb.OpenRecordset("GetData") "
    My query has one parameter named "Acct_No" on Table 1 "Account_Number" field.
    While running this code it is not asked me to enter this parameter, may be it create issue on this,

    i am just close to my project, please help me



  6. #6
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    Use one of these when parameters are in a query that is being used as a recordset.
    You likely need DAO version.
    http://www.accessmvp.com/thedbguy/co...?title=generic

    Make sure to set the parameter before use.

    It would have said Expected parameters in an error message?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  7. #7
    atuljadhav is offline Novice
    Windows 10 Office 365
    Join Date
    Aug 2022
    Posts
    15
    Hi,

    i modify my codes as below but still Run time error 3061 Too few Parameter. Expected 1. show
    i don't understand further

    while running this code it ask me to enter parameter which was set in "GetData" query, after enter getting above error and while debug yellow line show on
    " Set RS = db.OpenRecordset(qrySQL, dbOpenDynaset)" this line

    I just want to GetData from Two table and send email all the field selected in GetData query,
    i don't know where i stuck, since every customer has multiple lines so need to separate email by selecting in query.

    please help, i just close to finish this

    Code:
     Private Sub Command566_Click()
        'Opens the current access database
         Dim db As DAO.Database
         Set db = CurrentDb
         Dim RS As DAO.Recordset
         Dim EmailAdd As String
         'Mail Message MM
         Dim MM As String
         Dim qrySQL As String
    
         Set db = CurrentDb
    
        ' Set parameter values.
            
         DoCmd.SetParameter "[Acct_No]", "" & InputBox("Enter Account No:") & ""
            
         'Creates the SQL string - query contains just email addresses
          qrySQL = "SELECT * FROM GetData;"
          
         
         'creates a recordset (table) based on the sql Statement above
          Set RS = db.OpenRecordset(qrySQL, dbOpenDynaset)
    
    
          Do Until RS.EOF
              'creates the email string by reading the email from each record
              EmailAdd = EmailAdd & " ; " & RS!Email_Address
              'move next record RS!EMAIL
              RS.MoveNext
          Loop
    
        'creates Email body in HTML Format
    
         MM = "Dear Delegates,"
         MM = MM & "Blah blah blah"
    
         'create new email
         Set olook = CreateObject("outlook.application")
    
         Set oMail = olook.CreateItem(0)
         'Set parameters
         With oMail
             .To = EmailAdd
             .HTMLBody = MM
             .Subject = "Our title here"
             .CC = "address@address.com"
             .Display
         End With
     End Sub

  8. #8
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    You need to use the generic recordset link I posted.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  9. #9
    atuljadhav is offline Novice
    Windows 10 Office 365
    Join Date
    Aug 2022
    Posts
    15
    i think i am not able to explain my requirement, lets start from beginning

    I have Two table in access

    1) Open invoice data where Customer Code, Invoice No, Invoice Date, Due date, Amount etc columns are there.
    2) Customer master which has Customer Code, Name, Email_Address etc.

    I have created on query (Query name is "GetData") which merge this two table and get all field together, then i have created one parameter which filter on Account_No (Parameter name "Acct_No") because list is too huge so i will run only for once customer at one time.

    till this time all working fine,

    Then i created macro which run this query and send this output (query result) in HTML format in Body and get email address from query (GetData) "Email_Address" field and send email to that id only.

    Now the issue is, when i run this macro, it ask me to enter Account number, when i enter account no, getting above error.
    i have google it for solution but still not success.

    i hope this time i am cleared.

    Thanks in advance for help.

  10. #10
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    I believe I understand your requirement exactly.
    You need to evaluate the parameter that is the criteria for the query.
    To do that use that generic recordset code

    All you change is
    Code:
    Set RS = db.OpenRecordset(qrySQL, dbOpenDynaset)

    to
    Code:
    Set RS = fDAOGenericRst(qrySQL
    I have not seen it used with SetParameter, so you will have to experiment. Else set a Global or TempVar and use that as criteria.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  11. #11
    atuljadhav is offline Novice
    Windows 10 Office 365
    Join Date
    Aug 2022
    Posts
    15
    Not working,

    showing compile error: Sub or Function not defined

  12. #12
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    Have you copied the code to a module?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  13. #13
    atuljadhav is offline Novice
    Windows 10 Office 365
    Join Date
    Aug 2022
    Posts
    15
    Yes, i have changed the code in module as suggest by you,

  14. #14
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    Quote Originally Posted by atuljadhav View Post
    Yes, i have changed the code in module as suggest by you,
    But have you copied the code from that link and put it in your DB in a module.?

    You cannot use code you have not included in your DB?

    That is the only way I can see why it complains it cannot find the function, or you have mispelt it. The names have to be the same.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  15. #15
    atuljadhav is offline Novice
    Windows 10 Office 365
    Join Date
    Aug 2022
    Posts
    15
    Ok, i have added both the code mentioned on that link
    now it looks like below
    when i run this code it ask me to select macro "RTFBodyX" when i click then it show me error like "Compile Error: User-defined type not defined" on below lines

    Code:
    Function fADOGenericRst(ByVal strSource As String, _
        Optional cnn As ADODB.Connection, _
            Optional pCursorLocation As ADODB.CursorLocationEnum = adUseServer, _
            Optional pCursorType As ADODB.CursorTypeEnum = adOpenKeyset, _
            Optional pLockType As ADODB.LockTypeEnum = adLockOptimistic, _
            Optional pOption As ADODB.ExecuteOptionEnum = -1) As ADODB.Recordset

    i think i am making error in names but where don't know

    here is the full code in one module


    Code:
    Function fDAOGenericRst(strSQL As String, _
                        Optional intType As DAO.RecordsetTypeEnum = dbOpenDynaset, _
                        Optional intOptions As DAO.RecordsetOptionEnum, _
                        Optional intLock As DAO.LockTypeEnum, _
                        Optional pdb As DAO.Database) As DAO.Recordset
                                              
        Dim db As Database
        Dim qdf As QueryDef
        Dim rst As DAO.Recordset
        Dim prm As DAO.Parameter
        
        If Not pdb Is Nothing Then
            Set db = pdb
        Else
            Set db = CurrentDb
        End If
        
        On Error Resume Next
        Set qdf = db.QueryDefs(strSQL)
        If Err = 3265 Then
            Set qdf = db.CreateQueryDef("", strSQL)
        End If
        On Error GoTo 0
        
        For Each prm In qdf.Parameters
            prm.Value = Eval(prm.Name)
        Next
        
        If intOptions = 0 And intLock = 0 Then
            Set rst = qdf.fDAOGenericRst(intType)
        ElseIf intOptions > 0 And intLock = 0 Then
            Set rst = qdf.fDAOGenericRst(intType, intOptions)
        ElseIf intOptions = 0 And intLock > 0 Then
            Set rst = qdf.fDAOGenericRst(intType, intLock)
        ElseIf intOptions > 0 And intLock > 0 Then
            Set rst = qdf.fDAOGenericRst(intType, intOptions, intLock)
        End If
        Set fDAOGenericRst = rst
        
        Set prm = Nothing
        Set rst = Nothing
        Set qdf = Nothing
        Set db = Nothing
        
    End Function
    
    
    Function fADOGenericRst(ByVal strSource As String, _
        Optional cnn As ADODB.Connection, _
            Optional pCursorLocation As ADODB.CursorLocationEnum = adUseServer, _
            Optional pCursorType As ADODB.CursorTypeEnum = adOpenKeyset, _
            Optional pLockType As ADODB.LockTypeEnum = adLockOptimistic, _
            Optional pOption As ADODB.ExecuteOptionEnum = -1) As ADODB.Recordset
        
        Dim cmd As New ADODB.Command
        Dim prm As ADODB.Parameter
        
        If cnn Is Nothing Then
            Set cnn = CurrentProject.Connection
        End If
        Set cmd.ActiveConnection = cnn
        
        If Left(strSource, 11) <> "PARAMETERS " And Left(strSource, 7) <> "SELECT " Then
            strSource = "SELECT * FROM [" & strSource & "]"
        End If
        
        cmd.CommandText = strSource
        
        'cmd.Parameters.Refresh 'Is implicit - this is a Jet util so doesn't incur
        'overhead penalties
        For Each prm In cmd.Parameters
            prm.Value = Eval(prm.Name)
        Next
        
        Set fADOGenericRst = New ADODB.Recordset
        With fADOGenericRst
            .CursorLocation = pCursorLocation
            .Open cmd, , pCursorType, pLockType, pOption
        End With
        
        Set prm = Nothing
        Set cmd = Nothing
        
    End Function
    
     Sub RTFBodyX()
        'Opens the current access database
         Dim db As DAO.Database
         Set db = CurrentDb
         Dim RS As DAO.Recordset
         Dim EmailAdd As String
         'Mail Message MM
         Dim MM As String
         Dim qrySQL As String
    
         Set db = CurrentDb
    
        ' Set parameter values.
            
         DoCmd.SetParameter "[Acct_No]", "" & InputBox("Enter Account No:") & ""
            
         'Creates the SQL string - query contains just email addresses
          qrySQL = "SELECT * FROM GetData2;"
          
         
         'creates a recordset (table) based on the sql Statement above
          Set RS = fDAOGenericRst(qrySQL, dbOpenDynaset)
    
    
          Do Until RS.EOF
              'creates the email string by reading the email from each record
              EmailAdd = EmailAdd & " ; " & RS!Email_Address
              'move next record RS!EMAIL
              RS.MoveNext
          Loop
    
        'creates Email body in HTML Format
    
         MM = "Dear Delegates,"
         MM = MM & "Blah blah blah"
    
         'create new email
         Set olook = CreateObject("outlook.application")
    
         Set oMail = olook.CreateItem(0)
         'Set parameters
         With oMail
             .To = EmailAdd
             .HTMLBody = MM
             .Subject = "Our title here"
             .CC = "address@address.com"
             .Display
         End With
     End Sub

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 05-04-2017, 08:06 AM
  2. Replies: 1
    Last Post: 11-07-2016, 11:18 AM
  3. Email report to value (email address) in a field
    By JackieEVSC in forum Programming
    Replies: 7
    Last Post: 08-28-2015, 11:18 AM
  4. Automatically enter email address from customer table
    By Pure Salt in forum Import/Export Data
    Replies: 3
    Last Post: 07-09-2014, 08:03 AM
  5. Replies: 1
    Last Post: 05-01-2014, 11:37 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums