Results 1 to 14 of 14

Export Single record from form to csv file & email PDF File to chosen email address

  1. #1
    MikeWaring is offline Novice
    Windows 7 64bit Access 2002
    Join Date
    Dec 2010
    Posts
    28

    Red face Export Single record from form to csv file & email PDF File to chosen email address

    Hi, I'm just beginning to work with some VBA coding but I can't find the code I need to do all of the 3 particular tasks:

    1 - I need to be able to select the record being viewed in a single form and then to send this record an associated report that creates and prints a post label.
    2 - I need to be able to select the record being viewed in a single form and then to send this record to a csv file on my desktop.
    3 - In that record is the customer's email address. I need to attach a pdf form that is stored on my desktop and email it to the customer.

    My plan is to do this using 3 seperate command buttons as only one of tasks 1 and 2 will be done for any one record, depending on the need for a post label or a carrier label.

    Task 3 will be done for every record viewed. I've been able to previously send an email using the "SendObject" fucntion in a macro, but this was to a fixed email address in the "To" Field of the Action Arguments of the Object, but for this new task, I need to retrieve and use the email address from the customer's details.

    I've managed to get it to select the record and print the associated report (actually a post label), but I don't know the code for the 2 operations above. Here's the code for the label print that I've assigned as Event Procedure in the properties of the button.

    Code:
    Private Sub cmdPrint_Click()
        Dim strWhere As String
    
        If Me.Dirty Then    'Save any edits.
            Me.Dirty = False
        End If
    
        If Me.NewRecord Then 'Check there is a record to print
            MsgBox "Select a record to print"
        Else
            strWhere = "[ID] = " & Me.[ID]
            DoCmd.OpenReport "MyReport", acViewPreview, , strWhere
        End If
    End Sub
    .

    I'm sure this is simple for someone with VBA experieince, so if someone could "fill in the blanks" so to speak, I'd be very grateful.

    Thanks in advance


    Mike

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,222
    So item 1 is working?

    Item 2 could be an output from saved Access query with a parameter reference to control(s) on form for criteria using OutputTo or TransferText methods. Or you can open a text file object in VBA code and write data to that object.

    Item 3 - review https://www.accessforums.net/program...ook-21903.html
    This procedure could be called by each of the other 2.
    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
    MikeWaring is offline Novice
    Windows 7 64bit Access 2002
    Join Date
    Dec 2010
    Posts
    28
    Hi, thanks for the reply. Yes, item 1 is working perfectly - all I need now is to substitute parts of this code with those to enable me to perfrom the other 2 tasks. Each task will be assigned to a separate button, as task 1 is already assigned. I assume that this would be fairly straightforward, on the bais that if a can do task 1 directly from the form view, the correct code should also enable me to perform the other tasks in the same way?

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,222
    Yes.

    Also, if you want item 3 to automatically happen after either 1 or 2 then have 1 and 2 call the procedure for item 3.
    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
    MikeWaring is offline Novice
    Windows 7 64bit Access 2002
    Join Date
    Dec 2010
    Posts
    28

    Red face

    Hi June, thanks.

    I've now got items 1 and 2 working OK. I prefer to keep each item on a seperate button, as the tasks are usually performed at different times (ie the email is sent first then a few days later we create one or other of the labels).

    So, all I need now is the vba code to do the same job as the code below, but instead of opening a report and printing it, I need it to retrieve the customer's email address from the current record being viewed in the form and email a PDF attachment on the desktop.

    Would you be able to write the code, based on the one I've included below?

    The name of the form from which the current record is taken is called "GRA Records". The table / field that contains the email address is "Ebay Sales Records" / "Buyer email" (this table provides the data in a subform that is part of the main form "GRA Records". The PDF Document is called "GRA FORM and the filepath to it is "C:\Users\Mike\Desktop\"

    if I understand correctly, the part of the code
    Code:
     strWhere = "[Sales record number] = """ & Me.[Sales Record Number] & """"
    is the bit that ensures the current viewed record in the "GRA Records" form is the only record selected when the tasks run.

    Sorry to appear thick, but in all honesty when it comes to VBA I'm a pure novice just starting out; I'm learning as I get finished codes that work, but putting them together in the first place is my weakness.

    Here's the code that does the label print in item 1:

    Code:
    Private Sub cmdPrint_Click()
        Dim strWhere As String
    
        If Me.Dirty Then    'Save any edits.
            Me.Dirty = False
        End If
    
        If Me.NewRecord Then 'Check there is a record to print
            MsgBox "Select a record to print"
        Else
            strWhere = "[Sales record number] = """ & Me.[Sales Record Number] & """"
            DoCmd.OpenReport "4x3 Labels UK 2nd Class post label output GRA", acViewPreview, , strWhere
        End If
    End Sub
    Thanks in advance
    B Rgds
    Mike

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,222
    Post 2 has links to sample code for sending email. The one most relevant to your situation is http://forums.aspfree.com/microsoft-...ro-447084.html
    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
    MikeWaring is offline Novice
    Windows 7 64bit Access 2002
    Join Date
    Dec 2010
    Posts
    28
    Hi June, yes I saw that, but if I read it correctly, that is only for a fixed single email address. What I need it to do is to retrieve the email address from the customer's details so I can email him a PDF form. Each customer is unique so the same email address will rarely, if ever, be repeated. As I've said, I'm not VBA savvy enough to know how to do this.
    Rgds
    Mike

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,222
    Change the line that has "email address" to pull value from the current record on form:

    .Address = Me.EmailFieldname

    Use your actual fieldname.
    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
    MikeWaring is offline Novice
    Windows 7 64bit Access 2002
    Join Date
    Dec 2010
    Posts
    28
    Hi Jun, I've tried to put this together but I'm lost. Where do I insert the line that only selects the email address that is related to the record being viewed? I think the line I need is
    Code:
    strWhere = "[Sales record number] = """ & Me.[Sales Record Number] & """"
    Code:
    Private Sub cmdemailGRA_Click()
    'open Outlook, attach zip folder or file, send e-mail
    Dim appOutLook As Outlook.Application
    Dim MailOutLook As Outlook.MailItem
    Set appOutLook = CreateObject("Outlook.Application")
    Set MailOutLook = appOutLook.CreateItem(olMailItem)
    With MailOutLook
        .BodyFormat = olFormatRichText
        .Address = Me.EmailBuyer email
        ''.cc = ""
        ''.bcc = ""
        .Subject = "GRA Form"
        .HTMLBody = "text here"
        .Attachments.Add ("C:\Users\Mike\Desktop\GRA - FORM ONLY.pdf")
        ''.DeleteAfterSubmit = True 'This would let Outlook send the note without storing it in your sent bin
        .send
    End With
    End Sub

  10. #10
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,222
    No, that doesn't work and isn't necessary.

    The email address will be retrieved from the current record selected on the form. What is the name of the field that has the email address?

    .Address = Me.yourfieldnameHERE

    If the field name has spaces, special characters, punctuation (underscore is exception) then enclose in []:
    .Address = Me.[your field name HERE]

    You probably want some text in the body other than 'text here'.
    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.

  11. #11
    MikeWaring is offline Novice
    Windows 7 64bit Access 2002
    Join Date
    Dec 2010
    Posts
    28
    Hi June, thanks for the response.

    The name of the field is Buyer Email. I've corrected that part of it, but I'm now getting an error box "Compile Error" "User defined type not defined", and it highlights the line Dim appOutLook As Outlook.Application

    As I've mentioned before, I'm a novice to VBA (we all have to start sometime) so this error means nothing to me, consequently I don't know how to fix.

    Here's the current code:

    Code:
    Private Sub cmdemailGRA_Click()
    'open Outlook, attach zip folder or file, send e-mail
    Dim appOutLook As Outlook.Application
    Dim MailOutLook As Outlook.MailItem
    Set appOutLook = CreateObject("Outlook.Application")
    Set MailOutLook = appOutLook.CreateItem(olMailItem)
    With MailOutLook
        .BodyFormat = olFormatRichText
        .Address = Me.[Buyer Email]
        ''.cc = ""
        ''.bcc = ""
        .Subject = "GRA Form"
        .HTMLBody = "Hi, please find attached the GRA form. Please complete all parts to enable us to process the return as soon as possible. The Sales Record Number box on the form is for the Invoice number that can be found at the top right of the original invoice that was sent with the goods."
        
        .Attachments.Add ("C:\Users\Mike\Desktop\GRA - FORM ONLY.pdf")
        ''.DeleteAfterSubmit = True 'This would let Outlook send the note without storing it in your sent bin
        .send
    End With
    End Sub
    This bit is the only part stopping me from deploying the db, so any further help would be much appreciated..

    Best rgds
    Mike

  12. #12
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,222
    From post 2 of the referenced link:
    Be sure to set VBA reference for Microsoft Outlook 12.0 Object Library

    From the VBA editor menu bar: Tools > References > select the library from list
    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.

  13. #13
    MikeWaring is offline Novice
    Windows 7 64bit Access 2002
    Join Date
    Dec 2010
    Posts
    28
    Thanks. I fixed that - now i'm getting Run Time error "Microsoft Access can't find the field '|' referred to in your expression"
    When I hit the Debug button the line below is highlighted.

    .Address = Me.[Buyer email]

  14. #14
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,222
    The code is behind the form that has the field [Buyer email] included in RecordSource?

    Or is the button on a main form and record is in a subform? If so, what is name of the subform? I always name subform/subreport container control different from the object (table, query, form, report) the container holds. Like: ctrContacts.

    Reference to subform field:

    .Address = Me.ctrContacts![Buyer email]
    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.

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

Similar Threads

  1. Replies: 1
    Last Post: 03-15-2012, 05:41 PM
  2. Adding a email address on form
    By akhlaq768 in forum Forms
    Replies: 3
    Last Post: 02-07-2012, 09:11 AM
  3. Sending excel file in an email
    By Jim.H. in forum Access
    Replies: 1
    Last Post: 01-29-2012, 12:56 PM
  4. Replies: 6
    Last Post: 05-11-2010, 02:22 PM
  5. Replies: 1
    Last Post: 05-01-2009, 07:33 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
  •  
Tech Forums: Microsoft Office Forums