Results 1 to 7 of 7
  1. #1
    Join Date
    Dec 2005
    Posts
    21

    Answered: Sending EXCEL spread sheet out by Email

    I have found this code that I am able to save an Excel Spreadsheet of my database to my Hard drive or External depending where I open my database. I am trying to figure out how to send emails of this spreadsheet to seven other people. I have a table with the email addresses called 'Email' . I looked at several answers on this forum but it talks about PDF and going through record sets so I was confused. Can I modify this code or do I have to start out completely new? Thank you.

    Code:
    Private Sub btnExport_Click()
    Dim curPath As String
        Dim xlApp As Object
    
            curPath = CurrentProject.Path & "\Student - " & Format(Date, "mm-dd-yyyy")
            DoCmd.TransferSpreadsheet acExport, 10, "Student", curPath, -1
    
            Set xlApp = CreateObject("Excel.Application")
            xlApp.Workbooks.Open (curPath)
            xlApp.Visible = True
    End Sub

  2. Best Answer
    Posted by ranman256

    "In your sub, have the first line in the sub ,
    On error goto ErrSub

    And the last line before END SUB put,
    Code:
    Exit sub
    ErrSub:
    Msgbox err.description,,err
    Resume

    THEN you can step and find the line that fails."


  3. #2
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    495
    Provided Answers: 24
    there's no need to open the excel app
    'Set xlApp = CreateObject("Excel.Application")
    'xlApp.Workbooks.Open (curPath)
    'xlApp.Visible = True

    just use SENDOBJECT

    DoCmd.SendObject acSendQuery, qsQry, acFormatXLS, sTo, , , sSubj, sMsg

  4. #3
    Join Date
    Dec 2005
    Posts
    21
    Quote Originally Posted by ranman256 View Post
    there's no need to open the excel app
    'Set xlApp = CreateObject("Excel.Application")
    'xlApp.Workbooks.Open (curPath)
    'xlApp.Visible = True

    just use SENDOBJECT

    DoCmd.SendObject acSendQuery, qsQry, acFormatXLS, sTo, , , sSubj, sMsg
    Thank you. Here is what I have been working on from code I have found online. But I am getting an error. Here is what I have.

    Code:
    Options Explicit
    
    Private Sub btnEmail_Click()
    Dim FileName As String
    Dim FilePath As String
    Dim oOutlook As Outlook.Application
    Dim oEmailItem As MailItem
    Dim rs As Recordset
    Dim recipientList As String
    
    
    If oOutlook Is Nothing Then
        Set oOutlook = New Outlook.Application
    End If
    Set oEmailItem = oOutlook.CreateItem(olMailItem)
    With oEmailItem
            Set rs = CurrentDb.OpenRecordset("SELECT * FROM tbl_Customer")
            If rs.RecordCount > 0 Then
            rs.MoveFirst
            Do Until rs.EOF
                If IsNull(rs!email) Then
                rs.MoveNext
                Else
                    recipientList = recipientList & rs!email & ";"
                    .To = recipientList
                    rs.MoveNext
                End If
            Loop
            Else
                MsgBox "No one has email address!)"
            End If
            Set rs = Nothing
        .CC = ""
        .Subject = "Customer info: " & Me.CustomerName
        .Attachments.Add FilePath
        .Attachments.Add "F:\Microsoft Access Database I am Working on\Student.xlsx"
        .Display
    End With
    
    Set oEmailItem = Nothing
    Set oOutlook = Nothing
    
    End Sub
    I have the btnEmail on my User Form. When I click on the button the following error occurs:

    The Expression On Click you entered as the event property setting produced the following error: Invalid outside Procedure.
    * The expression may not result in the name of a macro, the name of a user-defined function, or [Event Procedure].
    * There may have been an error evaluating the function, event, or macro.

    I have a tbl_Customer that has the names of the customer plus there email address. Am I calling the table into the VBA incorrectly.

  5. #4
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    495
    Provided Answers: 24
    1. you didnt show where the error was.

    2. too much in the code...here it is simplified:

    Code:
            While Not rs.EOF
                If IsNull(rs!Email) Then
                Else
                    recipientList = recipientList & rs!Email & ";"
                End If
                
                rs.MoveNext
            Wend
           
           .To = recipientList
          .CC = ""
          .Subject = "Customer info: " & Me.CustomerName
         '  .Attachments.Add FilePath
        .Attachments.Add "F:\Microsoft Access Database I am Working on\Student.xlsx"
        .Display
    End With
    Set rs = Nothing

  6. #5
    Join Date
    Dec 2005
    Posts
    21
    Quote Originally Posted by ranman256 View Post
    1. you didnt show where the error was.

    2. too much in the code...here it is simplified:

    Code:
            While Not rs.EOF
                If IsNull(rs!Email) Then
                Else
                    recipientList = recipientList & rs!Email & ";"
                End If
                
                rs.MoveNext
            Wend
           
           .To = recipientList
          .CC = ""
          .Subject = "Customer info: " & Me.CustomerName
         '  .Attachments.Add FilePath
        .Attachments.Add "F:\Microsoft Access Database I am Working on\Student.xlsx"
        .Display
    End With
    Set rs = Nothing
    Thank you for your reply. I cannot get it to show me where the error is. It gives me the error code right away. I went into visual basic and tried to step through the code but it stops right away and just beeps. I am putting the code in the On Click Event Procedure. Should I put it in a module and call it from there?

  7. #6
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    495
    Provided Answers: 24
    In your sub, have the first line in the sub ,
    On error goto ErrSub

    And the last line before END SUB put,
    Code:
    Exit sub
    ErrSub:
    Msgbox err.description,,err
    Resume

    THEN you can step and find the line that fails.

  8. #7
    Join Date
    Dec 2005
    Posts
    21
    Quote Originally Posted by ranman256 View Post
    In your sub, have the first line in the sub ,
    On error goto ErrSub

    And the last line before END SUB put,
    Code:
    Exit sub
    ErrSub:
    Msgbox err.description,,err
    Resume

    THEN you can step and find the line that fails.
    Thank you I got it working using some other coding I found.

Posting Permissions

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