Email with Attachments

[Leesha]

Great question. Yes that should always be the same. The only difference would be whether it would be installed to OneDrive or Documents. I've suggested they always install to OneDrive so their data is backed up, not everyone is using OneDrive.

Also, this is what I now have in Module1:
Public glngClientID As Long
Public getstrPath As String

I put the code as suggested in the Load function. When the form loads I get an error that says that getStrPath is variable not defined.

[HansV]

getstrPath should be gstrPath

[HansV]

Copy the following code into a standard module (new or existing, just as you wish):

Code: Select all

Public Function Local_Name(theName As String) As String
    Dim i               As Integer
    Dim objShell        As Object
    Dim UserProfilePath As String

    ' Check if it looks like a OneDrive location.
    If InStr(1, theName, "https://", vbTextCompare) > 0 Then

        ' Replace forward slashes with back slashes.
        Local_Name = Replace(theName, "/", "\")

        'Get environment path using vbscript.
        Set objShell = CreateObject("WScript.Shell")
        UserProfilePath = objShell.ExpandEnvironmentStrings("%UserProfile%")

        ' Trim OneDrive designators.
        For i = 1 To 4
            Local_Name = Mid(Local_Name, InStr(Local_Name, "\") + 1)
        Next i

        ' Construct the name.
        Local_Name = UserProfilePath & "\OneDrive\" & Local_Name
    Else
        ' (must already be local).
        Local_Name = theName
    End If
End Function

The Form_Load procedure of the default form should look like this (plus perhaps other code you already had):

Code: Select all

Private Sub Form_Load()
    Dim folderPath As String
    folderPath = CurrentProject.Path
    If Right(folderPath, 1) <> "\" Then
        folderPath = folderPath & "\"
    End If
    folderPath = Local_Name(folderPath)
    If Right(folderPath, 1) <> "\" Then
        folderPath = folderPath & "\"
    End If
    gstrPath = folderPath & "EmailAttachments\"
End Sub

[Leesha]

I can't wait to try this out. I could NEVER have written this. Thanks so much!!

[Leesha]

Hi Hans,
I'm finally getting back to working on this and all seems well. My question is that when I click the command to send the email, Outlook opens with the mail in it and I then need to hit send from Outlook. Since this will most likely be used for bulk email, is there a way to have the code not open Outlook and just send out all of the emails?

Also I can't find the code to adjust the path to where the files are saved. Of course this has changed since I first starting building this. I looked in the three modules but didn't see anything specific there and I'm getting an error when trying it with a file attached stating the file can't be found and to verify the path. Since I oftentimes duplicate code you've given me in other things I'm working on, I need to understand where to make the necessary changes and this one is eluding me. :-)


Thanks,
Leesha

[HansV]

The code will work best if Outlook has already been started before the code is run. The code discussed in this thread has a line

.Send

This should send the message without user intervention.

My previous reply contains the code

Code: Select all

Private Sub Form_Load()
    Dim folderPath As String
    folderPath = CurrentProject.Path
    If Right(folderPath, 1) <> "\" Then
        folderPath = folderPath & "\"
    End If
    folderPath = Local_Name(folderPath)
    If Right(folderPath, 1) <> "\" Then
        folderPath = folderPath & "\"
    End If
    gstrPath = folderPath & "EmailAttachments\"
End Sub

This sets the variable gstrPath to the path of the subfolder EmailAttachments of the folder that contains the (frontend) database. This is the folder where the attachments are stored.

[Leesha]

Hi Hans,
I see where gstrPath mentions the folder path but I understand how the database knows what the folderpath is, and am assuming that is where I would make any changes/corrections if the folder name itself has actually changed or if I am using this code for another database. I've made the changes you had me do and "think" I have them correct but have probably gotten something mixed up. I'm getting an error that says the path does not exist and to verify that it is correct. I'm attaching the sample you sent with the revisions. I added frmLogon to load when the DB opens with the code that you instructed to put on the load event.
Can't wait to learn more,
Thanks,
Leesha

[HansV]

The variable gstrPath is set in the On Load event procedure of frmLogon. This procedure first retrieves the path of the database itself.
If that is a path on OneDrive, the code transforms that to the local name of the OneDrive folder. Finally, it appends the subfolder EmailAttachments.

For example, I saved the database from your attachment to my default Access folder C:\Users\zzzz\Documents\Access (where zzzz stands for my Windows username).
The On Load event procedure sets gstrPath to C:\Users\zzzz\Documents\Access\EmailAttachments\
As you see, this is the path of the database followed by \EmailAttachments\

If you want to get the attachments from a different folder, you will have to edit the On Load event procedure accordingly, in particular the line

Code: Select all

    gstrPath = folderPath & "EmailAttachments\"

[Leesha]

Hi Hans,
Please bear with me as I try to digest this.

If I am following you correctly, when frmLogon loads, "folderPath = CurrentProject.Path" automatically finds the DB regardless of where it is saved? If this is the case this is awesome since God only knows where users will put things.

I am still getting the path error. I believe it is because the EmailAttachments is no longer in the folder with the database but is now in a different folder, so the user can keep the files separated. This is what the path to EmailAttachments looks like now. I tried editing the code with this new path name but of course get errors. And yes the name of the DB has changed since I first started working on this with you as have associated folders due to business updates by the user.

C:\Users\Leesh\OneDrive\Documents\Trinity Solutions\TrinitySolutionsDatabaseFiles\EmailAttachments

Thanks!
Leesha

[HansV]

CurrentDb.Path does indeed return the path of the database, wherever the user has stored it.
How can we know where the end user stores the files to be attached?

[Leesha]

Wow! I am always so thrilled when you give me the explanation and I can make sense of it as I inevitably use your samples in other projects.

If I have anything to say about the user will always save the files to the path I gave above. If they aren't saved there and with the same name that is provided to them they will get errors. Sometimes that is the only way they learn. There are 10 standard attachments but the data may be different in them. IE the attachment Recipe.pdf can a variety of recipes. The instructions will be to save the print preview of the report to the EmailAttachment folder and to click on the corresponding file name, in this example Recipe.pdf. They are instructed NOT to change them name of the file. If they need to change the name of the file for some reason they are instructed to save it to another location. I'm sure there will be issues till they get used to it.

So, with that being said, provided there are no further folder changes, the path above should be firm. I say that with a laugh.

Leesha

[HansV]

C:\Users\Leesh\OneDrive\Documents\Trinity Solutions\TrinitySolutionsDatabaseFiles\EmailAttachments is a path on your computer.
Will the users have a folder Trinity Solutions with a subfolder TrinitySolutionsDatabaseFiles?
And will that be the folder that contains the database?

[Leesha]

They will use the same path but on their computer. So where is says Leesh it will have their name. There is a folder called. The actual DB lies in this route. C:\Users\Leesh\OneDrive\Documents\Trinity Solutions\TrinitySolutionsDatabase. I did try putting the EmailAttachments folder directly in the folder with the database to see if it would work but still got the same error.
Leesha

[HansV]

I am afraid that it is impossible for me to troubleshoot this. I am really sorry!

[Leesha]

OMG Hans you've never not been able to fix something. I'm totally in shock. Can you think of another solution that would work? I really appreciate your time and especially your brain!

[HansV]

You might do the following:

The first time the user opens the database, they get prompted to select the attachment folder.
This is then stored in a table, so that they don't have to be prompted every time.

[Leesha]

Would it work if the attachment path was automatically store in a table. The database has tblAdministrative which stores various items such as the username, password, computer name etc. Would I put the route to the folder in this table? I'm not sure if I'm visualizing this appropriately.

[Leesha]

This is what I tried. I stored the Emailattachments link in tblAdministrativeInformation. I created a query that pulls up the link. The I adjusted the code you gave to gstrPath = folderPath & "qryEmailAttachmentFolder.EmailAttachments\" I'm still getting the error re the path. What am I missing?
Thanks!

[HansV]

What does qryEmailAttachmentFolder look like?

[Leesha]

Sorry sent wrong sql ....
SELECT tblAdministrativeInformation.ID, tblAdministrativeInformation.EmailAttachments
FROM tblAdministrativeInformation
WHERE (((tblAdministrativeInformation.ID)=1));