Page 2 of 2 FirstFirst 12
Results 16 to 26 of 26
  1. #16
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Which line errors?

    Value is a reserved word. Should not use reserved words as names for variables or objects.



    Should include Option Explicit at top of every module. VBA editor can be set to do this automatically when module is created.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  2. #17
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Can you try this?
    Code:
    
    Dim  sFileName As String,  sEmail As String
    Dim rst As DAO.Recordset
    
    
    Set rst = CurrentDb.OpenRecordset("tbl_Email", dbOpenSnapshot)
    
    
    On Error Resume Next
    rst.MoveFirst
    
    
    Dim i As Integer
    For i = 1 To 10 'sending 10 emails individually
        sEmail = rst("Email")  'REPLACE WITH YOUR FIELD NAME
        sFileName = "W:\My Documents\Customers.txt"   
        Call vcSendEmail_Outlook("Your email subject", sEmail, , , sFileName, "")
    rst.MoveNext
    Next i
    
    
    rst.Close
    Set rst = Nothing
    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  3. #18
    talhaparvaiz@yahoo.com is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    May 2020
    Posts
    28
    Quote Originally Posted by Gicu View Post
    Can you try this?
    Code:
    
    Dim  sFileName As String,  sEmail As String
    Dim rst As DAO.Recordset
    
    
    Set rst = CurrentDb.OpenRecordset("tbl_Email", dbOpenSnapshot)
    
    
    On Error Resume Next
    rst.MoveFirst
    
    
    Dim i As Integer
    For i = 1 To 10 'sending 10 emails individually
        sEmail = rst("Email")  'REPLACE WITH YOUR FIELD NAME
        sFileName = "W:\My Documents\Customers.txt"   
        Call vcSendEmail_Outlook("Your email subject", sEmail, , , sFileName, "")
    rst.MoveNext
    Next i
    
    
    rst.Close
    Set rst = Nothing
    Cheers,


    --------I've made some modification and code below is working fine for following scenarios

    1) sending multiple emails (I've setup 2 for testing purposes)
    2) extracting multiple email addresses from the table and putting it in BCC ( thanks for the suggestion. it's a good option)

    ----------what is not working or remaining

    1) when I run the code it creates an email with the relevant data populated but then I am required to click "Send" button. How can I send the email without interruption

    2) my outlook currently has 2 accounts added to it. for some reason, whenever new email is created, it uses a non-default email address requiring manual change. how to always use a fixed "From" email address.

    3) is there a way to give a pause (lets say for a minute) between the emails

    Thanks again for all your assistance




    Code:
    Option Compare Database
    
    Sub Command0_Click()
    
    
    Dim sFileName As String, sEmail As String, BccEmail As String
    Dim rst As DAO.Recordset
    
    
    
    
    Set rst = CurrentDb.OpenRecordset("tbl_Email", dbOpenSnapshot) ' Table Name
    
    
    
    
    On Error Resume Next
    rst.MoveFirst
    
    
    
    
    Dim i As Integer
    Dim x As Integer
    
    
    For i = 1 To 2 'sending 2 emails individually
    
    
       sEmail = rst("Email_Address")  'Field Name
       CCEmail = sEmail
        BccEmail = ""
        
        For x = 1 To 5
        BccEmail = BccEmail & "," & sEmail
        rst.MoveNext
        sEmail = rst("Email_Address")
        Next x
        
            
        Call vcSendEmail_Outlook_With_Attachment("Subject", "to@info.com", "", BccEmail, "W:\My Documents\Customers.txt", "")
    
    
    
    
    Next i
    
    
    
    
    rst.Close
    Set rst = Nothing
    End Sub

  4. #19
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    For 1)

    Change

    OutMail.Display

    to

    OutMail.Send

    For 2) what do you mean by 'non-default', it should use account for user logged in to computer

    For 3) why?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #20
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  6. #21
    talhaparvaiz@yahoo.com is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    May 2020
    Posts
    28
    Quote Originally Posted by talhaparvaiz@yahoo.com View Post
    --------I've made some modification and code below is working fine for following scenarios

    1) sending multiple emails (I've setup 2 for testing purposes)
    2) extracting multiple email addresses from the table and putting it in BCC ( thanks for the suggestion. it's a good option)

    ----------what is not working or remaining

    1) when I run the code it creates an email with the relevant data populated but then I am required to click "Send" button. How can I send the email without interruption

    2) my outlook currently has 2 accounts added to it. for some reason, whenever new email is created, it uses a non-default email address requiring manual change. how to always use a fixed "From" email address.

    3) is there a way to give a pause (lets say for a minute) between the emails

    Thanks again for all your assistance




    Code:
    Option Compare Database
    
    Sub Command0_Click()
    
    
    Dim sFileName As String, sEmail As String, BccEmail As String
    Dim rst As DAO.Recordset
    
    
    
    
    Set rst = CurrentDb.OpenRecordset("tbl_Email", dbOpenSnapshot) ' Table Name
    
    
    
    
    On Error Resume Next
    rst.MoveFirst
    
    
    
    
    Dim i As Integer
    Dim x As Integer
    
    
    For i = 1 To 2 'sending 2 emails individually
    
    
       sEmail = rst("Email_Address")  'Field Name
       CCEmail = sEmail
        BccEmail = ""
        
        For x = 1 To 5
        BccEmail = BccEmail & "," & sEmail
        rst.MoveNext
        sEmail = rst("Email_Address")
        Next x
        
            
        Call vcSendEmail_Outlook_With_Attachment("Subject", "to@info.com", "", BccEmail, "W:\My Documents\Customers.txt", "")
    
    
    
    
    Next i
    
    
    
    
    rst.Close
    Set rst = Nothing
    End Sub

    Re #3. Let’s say, I have setup a yahoo and a Gmail account through the outlook and Gmail is setup as the primary account. When I send the emails through vba, I want to use gmail. But in my case yahoo is always set as a default when new email is created.


    Thanks

  7. #22
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #23
    talhaparvaiz@yahoo.com is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    May 2020
    Posts
    28
    Quote Originally Posted by pbaldy View Post

    The only problem with this approach is that it freezes my screen during the pause. Here is the code that I have used. Please advise



    Code:
    Option Compare Database
    
    Private Declare Sub sapiSleep Lib "kernel32" _
            Alias "Sleep" _
            (ByVal dwMilliseconds As Long)
    
    
    Sub sSleep(lngMilliSec As Long)
        If lngMilliSec > 0 Then
            Call sapiSleep(lngMilliSec)
        End If
    End Sub

  9. #24
    talhaparvaiz@yahoo.com is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    May 2020
    Posts
    28
    Quote Originally Posted by talhaparvaiz@yahoo.com View Post
    The only problem with this approach is that it freezes my screen during the pause. Here is the code that I have used. Please advise



    Code:
    Option Compare Database
    
    Private Declare Sub sapiSleep Lib "kernel32" _
            Alias "Sleep" _
            (ByVal dwMilliseconds As Long)
    
    
    Sub sSleep(lngMilliSec As Long)
        If lngMilliSec > 0 Then
            Call sapiSleep(lngMilliSec)
        End If
    End Sub

    I have found the solution...

    Code:
    Sub Wait_Time()
    Dim time1, time2
    
    time1 = Now
    time2 = Now + TimeValue("0:07:30")
        Do Until time1 >= time2
            DoEvents
            time1 = Now()
        Loop
    
    End Sub

  10. #25
    talhaparvaiz@yahoo.com is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    May 2020
    Posts
    28
    There is only one challenge remaining. - Following code/logic is good if I have 50 records or more.
    - for the scenarios, < 50 email records, it repeats the last email address from the table until reaches the count of 50.

    as an example, let's say I have 43 email address, following the logic below,
    - 8th email will have BCC email addresses as follows: #35, #36, #37, #38, #39, #40 (no issues with this one)
    - 9th email will have BCC email Addresses as follows: #40, #41, #42, #43, #43 (43 is repeated twice)
    - 10th email will have BCC email address as follows: #43, #43, #43, #43, #43 (43 is repeated 5 times)


    How can I modify the code to exit the loop if end of the table is reached



    Code:
    Sub Command0_Click()
    
    Dim sFileName As String, sEmail As String, BccEmail As String, eBody As String
    Dim rst As DAO.Recordset
    
    
    Set rst = CurrentDb.OpenRecordset("tbl_Email", dbOpenSnapshot) ' Table Name
    
     eBody = "<HTML><BODY>Message</BODY></HTML>"
    
    On Error Resume Next
    rst.MoveFirst
    
    
    Dim i As Integer
    Dim x As Integer
    
    For i = 1 To 10 'sending emails individually
    
        sEmail = rst("Email_Address")  'Field Name
        CCEmail = sEmail
        BccEmail = ""
        
        For x = 1 To 5 'Adding 5 email address to BCC
        BccEmail = BccEmail & ";" & sEmail
        rst.MoveNext
        sEmail = rst("Email_Address")
        Next x
        
            
        Call vcSendEmail_Outlook_With_Attachment("Subject", "To@email.com", "", BccEmail, "W:\My Documents\Customers.txt", eBody)
        Call Wait_Time
    Next i
    
    
    rst.Close
    Set rst = Nothing
    End Sub



    Thanks again for your assistance

  11. #26
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    You can use rst.EOF to check if you are at the last record and if yes exit loop.

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

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

Similar Threads

  1. Sending email through access 365
    By willmafingerdo in forum Access
    Replies: 7
    Last Post: 02-18-2020, 03:23 PM
  2. Sending a value from Access to Outlook email
    By George in forum Import/Export Data
    Replies: 8
    Last Post: 09-03-2018, 02:53 AM
  3. Replies: 1
    Last Post: 11-07-2016, 11:18 AM
  4. sending email through Access
    By crowegreg in forum Import/Export Data
    Replies: 4
    Last Post: 04-15-2012, 03:49 PM
  5. sending gmail email through access
    By TheShabz in forum Programming
    Replies: 20
    Last Post: 02-19-2012, 12:24 PM

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