Send email from Access

[dmarop]

Hello,

I use the following code in a script for email send and i will need your help to do some modifications.

        Dim AttachFile As String
        Dim DropBoxPath As String
        DropBoxPath = Environ("userprofile")
        AttachFile = DropBoxPath & "\Dropbox\mail.pdf"

        objMessage.AddAttachment AttachFile

I want in the AttachFile = to get the file name from a specific field of a table.
AttachFile = From the table EmailConfig and the field attachfile
and allows me to take more than one attachment. The second attachment to get it from the same table and the field

attachfile1.

Thank you in advance.

Regards,
Dimitris

 

[way2bord]

You should normalize your attachfile fields.


I think something like this might work.

for i = 0 to attachmentscount -1 'if you don't have this count, you'll need to do a fieldcount in tabledefs
[INDENT]if i > 0 then 
[INDENT]attachfile = "attachfile"
[/INDENT]else
[INDENT]attachfile = "attachfile" & i
[/INDENT]end if
 
set rs = currentdb.openrecordset("SELECT [EmailConfig].[" & attachfile & "] FROM [EmailConfig]")
 
rs.MoveFirst
Do until rs.EOF
[INDENT]objMessage.AddAttachment rs(attachfile)
rs.MoveNext
[/INDENT]Loop
[/INDENT]Next

 

[dmarop]

Hello,

Thank you for your reply. I tried to modify it but has error.

I upload an example in access if you can help me.

Have a nice a time,
Dimitris

 

[Frothingslosh]

I rewrote the code creating the email itself. See how this works.

Mostly I replaced this

        Set objMessage = CreateObject("CDO.Message")
        objMessage.subject = subject
        objMessage.from = from
        objMessage.To = reciever
        objMessage.TextBody = body
        
        'objMessage.AddAttachment "c:\temp\readme.txt"
        
        Dim AttachFile As String
        Dim DropBoxPath As String
        Dim attachmentscount As String
        DropBoxPath = Environ("userprofile")
            
        For i = 0 To attachmentscount - 1 'if you don't have this count, you'll need to do a fieldcount in tabledefs

            If i > 0 Then

                AttachFile = "attachfile"

            Else
        
                AttachFile = "attachfile" & i

            end if set rs = currentdb.openrecordset("SELECT [EmailConfig].[" & attachfile & "] FROM [EmailConfig]") rs.MoveFirst Do until rs.EOF
            
            objMessage.AddAttachment rs(attachfile) rs.MoveNext

            Loop

        Next

        'The line below shows how to send using HTML included directly in your script
        objMessage.HTMLBody = body

with this:

        Dim objMessage As Object
        Dim varAttachFile As Variant        'This must be a full path
        Dim varAttachFile1 As Variant       'This must be a full path
        Dim DropBoxPath As String
        Dim attachmentscount As String
        
        Set objMessage = CreateObject("CDO.Message")
        varAttachFile = DLookup("attachfile", "EmailConfig", "id=" & usid)
        varAttachFile1 = DLookup("attachfile1", "EmailConfig", "id=" & usid)
        
        'Create email object.
        With objMessage
            .subject = subject
            .from = from
            .to = reciever
            .body = body
            
            If Not IsNull(varAttachFile) And varAttachFile <> "" Then .Attachments.Add CStr(varAttachFile)
            If Not IsNull(varAttachFile1) And varAttachFile1 <> "" Then .Attachments.Add CStr(varAttachFile1)
        End With

        'The line below shows how to send using HTML included directly in your script
        'objMessage.HTMLBody = body

I also added option explicit and added a couple declarations that change required. Aaand it appears I forgot to remove dimensioning of attachmentscount and dropboxpath. :banghead:

 

[dmarop]

Hi,

Thank you again for your help.

I have replace the code as you said but has the following error:
Run-time error '438'
Object doesn't support this property or method.

Do you know why this happening? I attached the db to see exactly where is the problem.

Regards,
Dimitris

 

[Frothingslosh]

Question for you: I assume you're using Outlook for your email. Which version are you using? CDO isn't included with Outlook 2007, and isn't supported at all in Outlook 2010 or later.

That's not a big deal, though, because you can send emails with outlook directory as long as you activate the Outlook reference library in Access.

Edit: Okay, I'm looking at the whole file, now, not just the code. We're going to have to start by changing the code for the button you have calling the function. There is no need to have the only line for your command button call another function that is only used in that one instance. So it looks like I'm going to rework the Email button as well as the module. I'll give you an overview when I'm done.

I'm going to do this based on the assumption that you're using Outlook, since you're already using Access. If not, someone better at this than I will need to step in once I'm done, as I've only sent emails by Outlook programmatically.

If you are using Outlook, one thing you will need to do is open one of the modules, click on 'Tools' in the toolbars, then 'References', and make sure that your Outlook Object Library is checked. If it's not, find it and check it.

Anyway, I'll post more in a bit.

Edit 2: Bah, Gmail. Okay, time to do some research!

 

[Frothingslosh]

Okay, I found some code you could use as a basis if you want to stick with going through Gmail, but there's a catch. You'll need to either hardcode in a gmail login and password, or else the user will need to enter theirs each time.

If you're certain you want to use gmail, let me know which option you prefer. I have some code here that should do the trick for you. If you want to switch to Outlook, that's doable too without fiddling with Gmail logins and passwords.

 

[dmarop]

Hello,

Your help is very important and thank you very much for your time.

I use the Outlook 2007. I have activate the Microsoft Outlook 12.0 Object Library in Access.

I will be glad to hear from you.

Thanks,
Dimitris

 

[Frothingslosh]

Okay, that's the simpler method. Give me an hour or so and I should have something for you.

 

[dmarop]

For your previous answer: Ι will use the email of my company as email sender., which will be (info @ ....com)

The gmail just use it as an example.

Thanx again for your understanding.
Dimitris

 

[Frothingslosh]

Okay, here you go. For the purposes of this fix, I stripped out everything that wasn't needed for the problem at hand, so the Test table and form are gone, as is all the non-pertinent code.

Some things to keep in mind:

• Command buttons can be used to call functions and subs directly. There is no need to have them call a procedure that then calls the procedure you want to run.
• Look at the way the code for the command button references the form. That - me.controlname - is how you reference the value of a field on the form you ran the code from. If you need to reference a form other than the one that ran the code, you can still use [Forms]![formname].[controlname]

I included some very basic error checking for the recipient and subject fields, but you may want to get more in-depth.

I don't actually have Outlook set up at home (ironically, I use gmail through the web client), so I can't test this precise code myself, but it's based on code I used in multiple working apps I made at work, so it should work. My only real concern is the addition of two attachments, so let me know if that bombs.

*EDIT* Somehow the form got corrupted and couldn't execute any command buttons. I had to recreate it to get it to run. New version has been attached.

 

[dmarop]

Hello,

Thank you very much for your help. Have a nice time.

Dimitris