Struggling with an Access-Outlook Vba Code

[luffy1337]

Hello Friends... I've tried to learn a lot about Vba the last days. And i have to finish a Project where i dont have much time left. Does anybody of you have an idea how to solve my problem?



I have a Database and created some queries. The one query, that is important is "qrySchuldenMitEmail", where a column named e-mail is.
Additionally i have a report named "qryGesamtschulden"

My plan is, that with a click on a button, access opens "Outlook" and imports all the e-mails from my query "qrySchuldenMitEmail" + attaches my report named "qryGesamtschulden" to the E-mail

My following code already opens Outlook and attaches my report, but the thing with the e-mail addresses doesn't work:

Sub sendEmail()
Dim oOutlook As Outlook.Application
Dim oEmailItem As MailItem
Dim rs As Recordset
Dim customerEmail 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 [e-mail] from qrySchuldenMitEmail")
        If rs.RecordCount > 0 Then
        rs.MoveFirst
        Do Until rs.EOF
            If IsNull(rs![e-mail]) Then
            rs.MoveNext
            Else
                customerEmail = customerEmail & rs![e-mail] & ";"
                .To = customerEmail
                rs.MoveNext
        End If
        Loop
        Else
            MsgBox "Es gibt keine E-mail Adressen"
        End If
        
    .To = ""
    .Subject = "aktuelle Schuldenliste"
    .Display
End With
DoCmd.SendObject acSendReport, "qryGesamtschulden", "PDFFormat(*.pdf)"
End Sub

Private Sub Befehl3_Click()
Call sendEmail
End Sub

If somebody could fix my code and explain me what the problem is, i would be really really happy !!!

have a nice day,
luffy

[June7]

This is a common topic. Search forum for many threads.

What does "doesn't work" mean - error message, wrong results, nothing happens? Your code is really wonky. You are mixing two methods - SendObject and opening Outlook objects. Do one or the other, not both. The SendObject method does not reference the email address field from record.

Do you want to send multiple emails with the same report attached - same filter applied to report? Process like:

1. open recordset of email addresses

2. open filtered report object
DoCmd.OpenReport "report name", acViewPreview, , "filter criteria here"

3. begin loop to send multiple emails with SendObject method
DoCmd.SendObject acSendReport, , acFormatPDF, rs![e-mail], , , "aktuelle Schuldenliste"

4. move to next email record

5. repeat from step 3 until end of records

6. close report and close recordset

[pbaldy]

You've overwritten whatever the email loop did with this line:

.To = ""

[luffy1337]

oh, thank you for the fast replies.
@june7:
I'm sorry that i haven't explained enough what the problem is and what i want my code to do...

Basically i want my code to do the following:

1: I click on button "Befehl3"

2: Outlook opens with all the e-mails from the column "e-mail" from the query "qrySchuldenMitEmail" (with a semicolon so that i dont have to change anything in outlook)

3: "Subject" should say: Aktuelle Schuldenliste

4: my report named "qryGesamtSchulden" should be attached as a pdf file.

I don't want to send multiple E-Mails. I want only one Email to be created when i click on my button "Befehl3" and the e-mail should have all the e-mails from my query in the adress row.


What happens when i run this code:

1. Outlook opens

2. No E-Mails in Adress

3. Subject is empty

4. my report is attached

do you have an idea what i could change in my code to make it work? I may have mistakenly combined 2 different ways to solve this problem while reading through tutorials... i think the opening outlook object is needed, right?

@pbaldy thank you, sadly it doesn't help to only remove that line.

[June7]

Opening Outlook objects should not be needed when using SendObject method. Although I could be mistaken. My code works for me but I never close Outlook - it is always minimized to the task bar and available for viewing which means I am already logged in. Might also have to set Outlook properties to allow emails generated by code - File > Options > Trust Center > Trust Center Settings > Programmatic Access > I have it set to the default recommended level because we have current antivirus software and I never get the security warning.

If you want to concatenate all the addresses into one To string, this can be problematic with some network restrictions. Some network administrators set limits on how many addresses can be used in one email. However, the process could be:

1. open recordset of email addresses

2. begin loop to read addresses and concatenate into one string
strAddresses = rs![e-mail] & ";" & strAddresses

3. move to next record

4. repeat from step 2 until end of records

5. open report

6. send email with report as attached PDF using SendObject method
DoCmd.SendObject acSendReport, , acFormatPDF, strAddresses, , , "aktuelle Schuldenliste"

7. close report, close recordset

Consider that some people don't like their address indiscriminately distributed. If these are all fellow employees, not an issue. But might consider addressing the email to yourself and then assigning the address string to the BCC element.

[pbaldy]

You have combined methods. The email with an attachment is coming from the SendObject line, where you didn't fill in the subject or to arguments.

[luffy1337]

i followed your steps and managed to do it!!!!!!!!! thank you so much for your fast help!

thats the way my working code looks like:

Sub emailSenden()
Dim rs As Recordset
        Set rs = CurrentDb.OpenRecordset("select [e-mail] from qrySchuldenMitEmail")
        If rs.RecordCount > 0 Then
        rs.MoveFirst
        Do Until rs.EOF
            If IsNull(rs![e-mail]) Then
            rs.MoveNext
            Else
                strAddresses = rs![e-mail] & ";" & strAddresses
                rs.MoveNext
        End If
        Loop
        End If
DoCmd.SendObject acSendReport, "qryGesamtschulden", "PDFFormat (*.pdf)", strAddresses, , , "Aktuelle Schuldenliste"
End Sub

way easier than the way i tried before.

What would my opening outlook object have been good for? any idea?

i know about all the people who will get mailed and it is not an issue! thank you for your security advice!

[June7]

Outlook object good for attaching external files.