Results 1 to 10 of 10
  1. #1
    VWSpeedyB is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2015
    Location
    Work in Center Valley, PA, live near Philadelphia, PA
    Posts
    25

    Adding .XLS Format Attachment (existing Table) to VBA Gmail coding

    Good afternoon -

    I know there are multiple threads surrounding this topic. I have tried to follow and apply the various notes. However, because I have ZERO programming/code writing experience, I am quickly overwhelmed. The code to use Gmail is working for me from the VBA screed (test run). Now, can anyone help add the required coding to run an existing query that updates my table AND then attach the updated table to send as an XLS attachment?

    Second part to this, I need help "calling" the sub to run from the click of a designed button. Every time I try to send just the test e-mail, I get an error.

    Here is the existing Gmail code:

    Public Sub send_email()
    Set cdomsg = CreateObject("CDO.message")
    With cdomsg.Configuration.Fields


    .Item("http://schemas.microsoft.com/cdo/con...tion/sendusing") = 2 'NTLM method
    .Item("http://schemas.microsoft.com/cdo/con...ion/smtpserver") = "smtp.gmail.com"
    .Item("http://schemas.microsoft.com/cdo/con...smptserverport") = 465
    .Item("http://schemas.microsoft.com/cdo/con...tpauthenticate") = 1
    .Item("http://schemas.microsoft.com/cdo/con...ion/smtpusessl") = True
    .Item("http://schemas.microsoft.com/cdo/con...nectiontimeout") = 60
    .Item("http://schemas.microsoft.com/cdo/con...n/sendusername") = "mygmail@gmail.com" 'I know this is my e-mail
    .Item("http://schemas.microsoft.com/cdo/con...n/sendpassword") = "mypassword" 'I know this is my pw
    .Update
    End With
    ' build email parts
    With cdomsg
    .To = destination e-mail
    .From = "mygmail@gmail.com"
    .Subject = "the email subject"
    .TextBody = "the full message body goes here. you may want to create a variable to hold the text"
    .Send
    End With
    Set cdomsg = Nothing
    End Sub

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    What is the error message and what line triggers it?

    Calling the sub from button Click event with VBA should be simply:

    send_email()

    Select [Event Procedure] in the Click event property. Then click the ellipsis (...) to open the VBA editor. Type code.
    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.

  3. #3
    VWSpeedyB is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2015
    Location
    Work in Center Valley, PA, live near Philadelphia, PA
    Posts
    25
    June7 - thank you for the quick response. I've worked out the error on the button click, and it now sends the e-mail to me as reqeusted. I still don't understand how to incorporate the existing Query to update my table. Query name is Import_SKU_Extraction, which queries a master table for date meeting a specific flag, then updates a table, Import_SKU_List. I want to run the query, and then attach the updated table to my e-mail then complete the send. Can you help with that coding? Again, I am not a programmer, so I have no experience with this.

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    With this email method, have two options for including data with the email:

    1. save the data out to another file - PDF, Excel, Word - and then attach file to the email

    2. concatenate the data into a text string that can be part of the email body

    Common topic in forum, review https://www.accessforums.net/program...ort-30233.html

    Saving the data into another table can be done by several methods. How much data? Why do you need to save data into another table?
    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. #5
    VWSpeedyB is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2015
    Location
    Work in Center Valley, PA, live near Philadelphia, PA
    Posts
    25
    June -

    Here is my existing Code. If I can get a private sub to run within this, and have it create the data into an output file (.XLS) I don't need the extra table. I only did it that way because I want the subset, and that was the only way that I knew of to extract it.

    Public Sub Updated_Import_SKU_List()
    Set cdomsg = CreateObject("CDO.message")
    With cdomsg.Configuration.Fields
    .Item("http://schemas.microsoft.com/cdo/con...tion/sendusing") = 2 'NTLM method
    .Item("http://schemas.microsoft.com/cdo/con...ion/smtpserver") = "smtp.gmail.com"
    .Item("http://schemas.microsoft.com/cdo/con...smptserverport") = 465
    .Item("http://schemas.microsoft.com/cdo/con...tpauthenticate") = 1
    .Item("http://schemas.microsoft.com/cdo/con...ion/smtpusessl") = True
    .Item("http://schemas.microsoft.com/cdo/con...nectiontimeout") = 60
    .Item("http://schemas.microsoft.com/cdo/con...n/sendusername") = "xxxxxxxxxxxxxx"
    .Item("http://schemas.microsoft.com/cdo/con...n/sendpassword") = "xxxxxx"
    .Item("http://schemas.microsoft.com/cdo/con...ion/SendObject") = "Import_SKU_List"
    .Update
    End With
    ' build email parts
    With cdomsg
    .To = "xxxxx"
    .BCC = "xxxxx"
    .From = "xxxxxx"
    .Subject = "Updated Import SKU File"
    .TextBody = "TEST EMAIL"
    .SendObject
    End With
    Set cdomsg = Nothing
    End Sub

    Public Function Send_Import_Databse()
    Call Updated_Import_SKU_List
    End Function

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    You don't need another sub. Just have code in this sub that opens query or report object and saves it to Excel or PDF. The referenced link has example.

    Pseudocode:

    Open query or report
    Output query or report to Excel or PDF
    Close query or report
    Send email with attachment
    Delete the Excel or PDF if want to
    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.

  7. #7
    VWSpeedyB is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2015
    Location
    Work in Center Valley, PA, live near Philadelphia, PA
    Posts
    25
    June -

    I've almost got it. However, I'm receiving an error on my attachment line. The error reads "Object doesn't support this property or method".

    Here's the code:

    Public Sub Updated_Import_SKU_List()
    Set cdomsg = CreateObject("CDO.message")
    With cdomsg.Configuration.Fields
    .Item("http://schemas.microsoft.com/cdo/con...tion/sendusing") = 2 'NTLM method
    .Item("http://schemas.microsoft.com/cdo/con...ion/smtpserver") = "smtp.gmail.com"
    .Item("http://schemas.microsoft.com/cdo/con...smptserverport") = 465
    .Item("http://schemas.microsoft.com/cdo/con...tpauthenticate") = 1
    .Item("http://schemas.microsoft.com/cdo/con...ion/smtpusessl") = True
    .Item("http://schemas.microsoft.com/cdo/con...nectiontimeout") = 60
    .Item("http://schemas.microsoft.com/cdo/con...n/sendusername") = "XXX"
    .Item("http://schemas.microsoft.com/cdo/con...n/sendpassword") = "XXX"
    .Update
    End With
    With cdomsg
    DoCmd.OpenQuery "Import_SKU_Extraction"
    DoCmd.OutputTo acTable, "Import_SKU_List", acFormatXLSX, "Z:\Web-Site Updates\Broker Import Database\Database Information\Extracted Tables\Import_SKU_List"
    End With
    ' build email parts
    With cdomsg
    .To = "XXX"
    .BCC = "XXX"
    .From = "XXX"
    .Subject = "Updated Import SKU File"
    .TextBody = "TEST EMAIL"
    .AttachmentAdd = "Z:\Web-Site Updates\Broker Import Database\Database Information\Extracted Tables\Import_SKU_List.XLSX"
    .Send
    End With

    Set cdomsg = Nothing
    End Sub

    Public Function Send_Import_Databse()
    Call Updated_Import_SKU_List
    End Function
    Last edited by VWSpeedyB; 01-30-2015 at 03:12 PM. Reason: Quote the code used

  8. #8
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    I don't know if GMail would be different, but examples for CDO I find show AddAttachment instead of AttachmentAdd.

    https://msdn.microsoft.com/en-us/lib...EXCHG.65).aspx

    With Outlook I use:

    .Attachments.add ("file path\name")
    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.

  9. #9
    VWSpeedyB is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2015
    Location
    Work in Center Valley, PA, live near Philadelphia, PA
    Posts
    25
    Ok, changed line to read - .Attachments.Add ("path\name") and receive a Type Mismatch error.

    I've played with including and excluding the ".xlsx" at the end of the file name, with no difference.

  10. #10
    VWSpeedyB is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2015
    Location
    Work in Center Valley, PA, live near Philadelphia, PA
    Posts
    25
    PROBLEM SOLVED!!!!!!!


    Thank you so much for your assistance!!!!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. I am stumped...adding field to existing table
    By tanyalee123 in forum Queries
    Replies: 6
    Last Post: 12-09-2013, 06:04 PM
  2. Replies: 5
    Last Post: 11-01-2013, 09:34 AM
  3. Adding data from linked table to existing table???
    By claysea in forum Import/Export Data
    Replies: 3
    Last Post: 02-21-2013, 12:23 PM
  4. Replies: 1
    Last Post: 01-31-2012, 11:54 AM
  5. Adding records to existing table
    By Mclaren in forum Programming
    Replies: 5
    Last Post: 05-18-2011, 12:44 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