Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2006
    Posts
    162

    Unanswered: Email data using vba

    I"m wanting to automate sending emails with data attached them.

    What I have-
    Table with names and emails.
    Query with data containing names as one of the records.

    What I want to do is send the data for each use to their email and omit data that is not their own.

    First question should I use a winsock, blat, or something else.

    Next question, whats the best way to get the data to them excel file, pdf, report. I would prefer excel so they could sort the data if they wanted to, but will the process have to open, past, save, then close a file for each user?

    Last question, how would I seperate the data after the query has run with the user names.


    TIA

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    I've used Blat very successfully. Mainly due to Outlook (newer versions) popping up with the message asking to click "Yes" to continue sending the email with the attachment. I used Blat to send emails with attachments without popping up any message. I put in some code which exported a query to excel (or *.csv) and then attached that exported file to the email. It worked very well and didn't require sending each email separately. If you can't find the vba code to send the email via Blat, let me know and I'll try to dig it up somewhere. It was only about 20 lines of code to do the whole thing. Otherwise if you have a newer version of Outlook (I think it was actually in a service pack where they made it so the popup message appears), it's a pain-in-the-@## to remove the security so you don't get the popup message (I'm not sure what Microsoft was thinking here but I guess too many programmers abused the sending emails with attachments - #@!$ Microsoft - I have to say this is one of my least favorite upgrades they made.)

    I'd recommend exporting to a *.csv formatted file though as this is basically a text file (comma separated) and will be the smallest and allow the capability to import the data (or open it with Excel). Exporting to Excel will throw in the other Excel information in the exported file which can end up in a bloated file.

    Not sure what you mean by separating the data though with the user names. I also used a make-table query to make the export table first and then exported that table. You could do the same thing and make the table with the getuser() name (found in the code bank) which retrieves the LoginID for that user. You may have to write a bit of code to do the make-table instead of a make-table query so you can add the user's loginID to the name of the table which is being created. If you mean something else here, you can still use the getuser() routine as either an expression in your query (or to populate as a field in your make-table code) or other ways.

    Hope this helps.
    Last edited by pkstormy; 12-18-07 at 22:55.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Sep 2006
    Posts
    162
    Thanks bro, i'll look at that.

    What I meant when I said user, was I have a database of names, with employee number, and email.

    Each line of data contains the employee number, I want to send the data for each employee to their email.

  4. #4
    Join Date
    Sep 2006
    Posts
    162
    Yeah, i've been looking around. Can;t find much on sending email with Blat from access.

  5. #5
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6

  6. #6
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Here's some code I had on sending an email with an attachment via blat...

    Public Sub EmailFunctionBlat(MsgBody As String)
    Dim ToVar, FromVar, Subject As String
    FromVar = GetUser() & "@weccusa.org"
    ToVar = "paulk@weccusa.org"
    Server = "linux2.weccusa.org"
    FileToSend = "\\SQLServer\Databases\Databases\blatEmail\message .txt"
    'Get Message and send email via blat
    Subject = "DOA Reporting now running (blat)"
    Dim x As String
    p_blat_location = "\\SQLServer\Databases\Databases\blatEmail\blat.ex e"
    x = p_blat_location & " " & FileToSend & " -s " & Chr(34) & Subject & Chr(34) & _
    " -t " & ToVar & " -f " & FromVar & " -server " & Server & " -body " & Chr(34) & MsgBody & Chr(34)
    Debug.Print x
    Shell x, vbHide
    End Sub
    Last edited by pkstormy; 12-22-07 at 23:29.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •