Email with Attachments
-
- BronzeLounger
- Posts: 1488
- Joined: 05 Feb 2010, 22:25
Re: Email with Attachments
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.
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.
-
- Administrator
- Posts: 78535
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
-
- Administrator
- Posts: 78535
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Email with Attachments
Copy the following code into a standard module (new or existing, just as you wish):
The Form_Load procedure of the default form should look like this (plus perhaps other code you already had):
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
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
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1488
- Joined: 05 Feb 2010, 22:25
Re: Email with Attachments
I can't wait to try this out. I could NEVER have written this. Thanks so much!!
-
- BronzeLounger
- Posts: 1488
- Joined: 05 Feb 2010, 22:25
Re: Email with Attachments
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
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
-
- Administrator
- Posts: 78535
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Email with Attachments
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
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.
.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
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1488
- Joined: 05 Feb 2010, 22:25
Re: Email with Attachments
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
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
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78535
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Email with Attachments
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
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\"
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1488
- Joined: 05 Feb 2010, 22:25
Re: Email with Attachments
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
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
-
- Administrator
- Posts: 78535
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Email with Attachments
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?
How can we know where the end user stores the files to be attached?
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1488
- Joined: 05 Feb 2010, 22:25
Re: Email with Attachments
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
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
-
- Administrator
- Posts: 78535
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Email with Attachments
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?
Will the users have a folder Trinity Solutions with a subfolder TrinitySolutionsDatabaseFiles?
And will that be the folder that contains the database?
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1488
- Joined: 05 Feb 2010, 22:25
Re: Email with Attachments
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
Leesha
-
- Administrator
- Posts: 78535
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Email with Attachments
I am afraid that it is impossible for me to troubleshoot this. I am really sorry!
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1488
- Joined: 05 Feb 2010, 22:25
Re: Email with Attachments
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!
-
- Administrator
- Posts: 78535
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Email with Attachments
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.
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.
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1488
- Joined: 05 Feb 2010, 22:25
Re: Email with Attachments
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.
-
- BronzeLounger
- Posts: 1488
- Joined: 05 Feb 2010, 22:25
Re: Email with Attachments
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!
Thanks!
-
- Administrator
- Posts: 78535
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
-
- BronzeLounger
- Posts: 1488
- Joined: 05 Feb 2010, 22:25
Re: Email with Attachments
Sorry sent wrong sql ....
SELECT tblAdministrativeInformation.ID, tblAdministrativeInformation.EmailAttachments
FROM tblAdministrativeInformation
WHERE (((tblAdministrativeInformation.ID)=1));
SELECT tblAdministrativeInformation.ID, tblAdministrativeInformation.EmailAttachments
FROM tblAdministrativeInformation
WHERE (((tblAdministrativeInformation.ID)=1));