3

I need to set the path so that it can be opened on any desktop.

This is the code I have so far:

Dim myOlApp As Outlook.Application
Dim myitem As Outlook.MailItem
Dim n As Integer

Set myOlApp = CreateObject("Outlook.Application")
Set myitem = myOlApp.CreateItemFromTemplate("C:\Users\User\documents\Template.msg")

With myitem

For n = 0 To Me.EmailList.ListCount - 1
.Attachments.Add (Me.EmailList.ItemData(n))
Next n

myitem.Subject = Nz("")
myitem.To = Nz(Me.txtCustomerEmailAddress1)
myitem.Display
DragonSamu
  • 1,163
  • 10
  • 18
Neilster
  • 31
  • 2

2 Answers2

1

Since you tagged the question as access-vba, I am assuming that you are sending email from within ms access. I am also assuming you are referring to path to Template.msg. If so, you can store Template.msg to a table in MS Access (as binary data) and then just before sending the email, you save it to disk. Your code would be something like:

Set myOlApp = CreateObject("Outlook.Application")
...
dim filesPath as String
filesPath = Environ$("USERPROFILE") ' this will return the current user's folder 
Call writeTemplate(filesPath) ' this will write Template.msg from table to a file in filesPath path
Set myitem = myOlApp.CreateItemFromTemplate(filesPath & "\Template.msg")

In "writeTemplate()" method you need to just save Template.msg to disk, in directory filesPath.

chrisl08
  • 1,658
  • 1
  • 15
  • 24
  • Thanks chrisl08 for that. I have declaired Dim sUsername As String, however I get 'Compile Error' Sub or function not difined on the below line. Call writeTemplate(filesPath). As yes you are right i have a button on a form in access that picks up the template in the documents folder. – Neilster Sep 06 '15 at 08:17
  • Which line you are getting "Sub or function not difined" ? – chrisl08 Sep 06 '15 at 08:20
  • 1
    @Neilster If you are referring to writeTemplate() method , I left that for you to implement :-) . Here is an example: https://support.microsoft.com/en-us/kb/103257 – chrisl08 Sep 06 '15 at 08:22
  • Thanks chrisl08 I have finaly sorted it I think. Thanks for all your help. (-: – Neilster Sep 06 '15 at 08:36
  • Always glad to help. Would you consider marking my answer as correct then? Thanks :-) – chrisl08 Sep 06 '15 at 09:10
  • Sure no worries, how do I do that? the other thing is I can open the email when Outlook is open but not when closed, I've tried an 'On Error GoTo Error_Handler, but nothing seems to work, any advise much appreciated.(-: – Neilster Sep 07 '15 at 09:29
0

When doing such things, I strongly advise you to use the environ() function of VBA, which will return contextual pathes depending on users and/or machines. You can try the following in your debug window:

environ("username")
environ("userdomain")

You can retrieve all environment variables through environ()

some interesting code about environ() can be found here and there

Community
  • 1
  • 1
Philippe Grondier
  • 10,900
  • 3
  • 33
  • 72