Mixing
Save email as .msg in local windows folder using Excel VBA
up vote
0
down vote
favorite
I want to save email in my local folder using Excel VBA.
I saw this link https://www.mrexcel.com/forum/excel-questions/361751-vba-saving-email-only-after-send-pushed.html which uses a class module to save the email. It stores the email at the same time it opens it for display. The email saved is the draft email. You can still edit the saved .msg file.
How would I wait until the email is sent? Presumably once it detects the email in the Outlook 'Sent Items' folder?
Dim cls_OL As New clsOutlook
Public outMail As Outlook.MailItem
Public Emailpath As String
Sub SendEmail()
Dim objItems As Items
Dim objApp As Object
Set objApp = CreateObject("Outlook.Application")
Set cls_OL.obj_OL = GetObject(Class:="Outlook.Application")
Set OutMail = objItems.Add
Emailpath = "V:testemailname.msg"
With OutMail
On Error Resume Next
.HTMLBody = "Hi All, This is test email"
.to = "test@test.com"
.CC = vbnullstring
.BCC = vbnullstring
.Subject = "A Subject"
.Display
End With
Set OutMail = Nothing
End Sub
.
Option Explicit
Public WithEvents obj_OL As Outlook.Application
Private Sub obj_OL_ItemSend(ByVal Item As Object, Cancel As Boolean)
Item.SaveAs Emailpath
Set obj_OL = Nothing
Set outMail = Nothing
End Sub
This is the email that is being saved:
This is what I want to save:
Edit with the suggestion from Dmitry
Dim cls_OL As New clsOutlook
Public outMail As Outlook.MailItem
Public Emailpath As String
Sub SendEmail()
Dim objItems As Items
Dim Emailpath as string
Dim objApp as object
Set objApp = CreateObject("Outlook.Application")
Set objItems = objApp.Session.GetDefaultFolder(olFolderSentMail).Items
Set OutMail = objItems.Add
Emailpath = "V:testemailname.msg"
With OutMail
.HTMLBody = "Hi All, This is test email"
.to = "test@test.com"
.CC = vbnullstring
.BCC = vbnullstring
.Subject = "A Subject"
.Display
End With
Set OutMail = Nothing
End Sub
.
Option Explicit
Public WithEvents objItems As Outlook.Application
Private Sub objItems_ItemAdd(ByVal Item As Object)
Item.SaveAs Emailpath
Set obj_OL = Nothing
Set outMail = Nothing
End Sub
excel vba email outlook
asked Oct 31 at 3:05
Brian Chew
64
add a comment |
up vote
0
down vote
favorite
I want to save email in my local folder using Excel VBA.
I saw this link https://www.mrexcel.com/forum/excel-questions/361751-vba-saving-email-only-after-send-pushed.html which uses a class module to save the email. It stores the email at the same time it opens it for display. The email saved is the draft email. You can still edit the saved .msg file.
How would I wait until the email is sent? Presumably once it detects the email in the Outlook 'Sent Items' folder?
Dim cls_OL As New clsOutlook
Public outMail As Outlook.MailItem
Public Emailpath As String
Sub SendEmail()
Dim objItems As Items
Dim objApp As Object
Set objApp = CreateObject("Outlook.Application")
Set cls_OL.obj_OL = GetObject(Class:="Outlook.Application")
Set OutMail = objItems.Add
Emailpath = "V:testemailname.msg"
With OutMail
On Error Resume Next
.HTMLBody = "Hi All, This is test email"
.to = "test@test.com"
.CC = vbnullstring
.BCC = vbnullstring
.Subject = "A Subject"
.Display
End With
Set OutMail = Nothing
End Sub
.
Option Explicit
Public WithEvents obj_OL As Outlook.Application
Private Sub obj_OL_ItemSend(ByVal Item As Object, Cancel As Boolean)
Item.SaveAs Emailpath
Set obj_OL = Nothing
Set outMail = Nothing
End Sub
This is the email that is being saved:
This is what I want to save:
Edit with the suggestion from Dmitry
Dim cls_OL As New clsOutlook
Public outMail As Outlook.MailItem
Public Emailpath As String
Sub SendEmail()
Dim objItems As Items
Dim Emailpath as string
Dim objApp as object
Set objApp = CreateObject("Outlook.Application")
Set objItems = objApp.Session.GetDefaultFolder(olFolderSentMail).Items
Set OutMail = objItems.Add
Emailpath = "V:testemailname.msg"
With OutMail
.HTMLBody = "Hi All, This is test email"
.to = "test@test.com"
.CC = vbnullstring
.BCC = vbnullstring
.Subject = "A Subject"
.Display
End With
Set OutMail = Nothing
End Sub
.
Option Explicit
Public WithEvents objItems As Outlook.Application
Private Sub objItems_ItemAdd(ByVal Item As Object)
Item.SaveAs Emailpath
Set obj_OL = Nothing
Set outMail = Nothing
End Sub
excel vba email outlook
asked Oct 31 at 3:05
Brian Chew
64
add a comment |
up vote
0
down vote
favorite
up vote
0
down vote
favorite
I want to save email in my local folder using Excel VBA.
I saw this link https://www.mrexcel.com/forum/excel-questions/361751-vba-saving-email-only-after-send-pushed.html which uses a class module to save the email. It stores the email at the same time it opens it for display. The email saved is the draft email. You can still edit the saved .msg file.
How would I wait until the email is sent? Presumably once it detects the email in the Outlook 'Sent Items' folder?
Dim cls_OL As New clsOutlook
Public outMail As Outlook.MailItem
Public Emailpath As String
Sub SendEmail()
Dim objItems As Items
Dim objApp As Object
Set objApp = CreateObject("Outlook.Application")
Set cls_OL.obj_OL = GetObject(Class:="Outlook.Application")
Set OutMail = objItems.Add
Emailpath = "V:testemailname.msg"
With OutMail
On Error Resume Next
.HTMLBody = "Hi All, This is test email"
.to = "test@test.com"
.CC = vbnullstring
.BCC = vbnullstring
.Subject = "A Subject"
.Display
End With
Set OutMail = Nothing
End Sub
.
Option Explicit
Public WithEvents obj_OL As Outlook.Application
Private Sub obj_OL_ItemSend(ByVal Item As Object, Cancel As Boolean)
Item.SaveAs Emailpath
Set obj_OL = Nothing
Set outMail = Nothing
End Sub
This is the email that is being saved:
This is what I want to save:
Edit with the suggestion from Dmitry
Dim cls_OL As New clsOutlook
Public outMail As Outlook.MailItem
Public Emailpath As String
Sub SendEmail()
Dim objItems As Items
Dim Emailpath as string
Dim objApp as object
Set objApp = CreateObject("Outlook.Application")
Set objItems = objApp.Session.GetDefaultFolder(olFolderSentMail).Items
Set OutMail = objItems.Add
Emailpath = "V:testemailname.msg"
With OutMail
.HTMLBody = "Hi All, This is test email"
.to = "test@test.com"
.CC = vbnullstring
.BCC = vbnullstring
.Subject = "A Subject"
.Display
End With
Set OutMail = Nothing
End Sub
.
Option Explicit
Public WithEvents objItems As Outlook.Application
Private Sub objItems_ItemAdd(ByVal Item As Object)
Item.SaveAs Emailpath
Set obj_OL = Nothing
Set outMail = Nothing
End Sub
excel vba email outlook
asked Oct 31 at 3:05
Brian Chew
64
I want to save email in my local folder using Excel VBA.
I saw this link https://www.mrexcel.com/forum/excel-questions/361751-vba-saving-email-only-after-send-pushed.html which uses a class module to save the email. It stores the email at the same time it opens it for display. The email saved is the draft email. You can still edit the saved .msg file.
How would I wait until the email is sent? Presumably once it detects the email in the Outlook 'Sent Items' folder?
Dim cls_OL As New clsOutlook
Public outMail As Outlook.MailItem
Public Emailpath As String
Sub SendEmail()
Dim objItems As Items
Dim objApp As Object
Set objApp = CreateObject("Outlook.Application")
Set cls_OL.obj_OL = GetObject(Class:="Outlook.Application")
Set OutMail = objItems.Add
Emailpath = "V:testemailname.msg"
With OutMail
On Error Resume Next
.HTMLBody = "Hi All, This is test email"
.to = "test@test.com"
.CC = vbnullstring
.BCC = vbnullstring
.Subject = "A Subject"
.Display
End With
Set OutMail = Nothing
End Sub
.
Option Explicit
Public WithEvents obj_OL As Outlook.Application
Private Sub obj_OL_ItemSend(ByVal Item As Object, Cancel As Boolean)
Item.SaveAs Emailpath
Set obj_OL = Nothing
Set outMail = Nothing
End Sub
This is the email that is being saved:
This is what I want to save:
Edit with the suggestion from Dmitry
Dim cls_OL As New clsOutlook
Public outMail As Outlook.MailItem
Public Emailpath As String
Sub SendEmail()
Dim objItems As Items
Dim Emailpath as string
Dim objApp as object
Set objApp = CreateObject("Outlook.Application")
Set objItems = objApp.Session.GetDefaultFolder(olFolderSentMail).Items
Set OutMail = objItems.Add
Emailpath = "V:testemailname.msg"
With OutMail
.HTMLBody = "Hi All, This is test email"
.to = "test@test.com"
.CC = vbnullstring
.BCC = vbnullstring
.Subject = "A Subject"
.Display
End With
Set OutMail = Nothing
End Sub
.
Option Explicit
Public WithEvents objItems As Outlook.Application
Private Sub objItems_ItemAdd(ByVal Item As Object)
Item.SaveAs Emailpath
Set obj_OL = Nothing
Set outMail = Nothing
End Sub
excel vba email outlook
excel vba email outlook
asked Oct 31 at 3:05
Brian Chew
64
asked Oct 31 at 3:05
Brian Chew
64
edited 4 hours ago
asked Oct 31 at 3:05
Brian Chew
64
asked Oct 31 at 3:05
Brian Chew
64
asked Oct 31 at 3:05
Brian Chew
64
64
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
up vote
0
down vote
You can listen for the Items.ItemAdd event on the Sent Items folder (you can retrieve it using Namespace.GetDEfaultFolder(olFolderSentMail)).
EDIT: off the top of my head:
public WithEvents objItems As Outlook.Items
set objItems = Application.Session.GetDefaultFolder(olFolderSentMail).Items
...
Private objItems_ItemAdd(ByVal Item As Object)
Item.SaveAs Emailpath
End Sub
answered Oct 31 at 22:20
Dmitry Streblechenko
41.7k32760
Hi @dmitry, Thank you for replying. I don't really get what do you mean by listen for it. Any codes that I need to change. Sorry that I kind of new with Class Module.
– Brian Chew
Nov 1 at 2:11
Declare a global Items "with events" (like you do with obj_OL) variable and provide the ItemAdd event handler. Items collection comes from MAPIFolder.Items, and MAPIFolder is retrieved from Namespace.GetDEfaultFolder(olFolderSentMail)
– Dmitry Streblechenko
Nov 1 at 2:18
I am really sorry I don't get what you mean. This is my attempt. I am really sorry because I dont have any background with this. Public WithEvents obj_OL As Outlook.Application Private Sub obj_OL_ItemAdd(ByVal Item As Object, Cancel As Boolean) Item.ItemAdd Namespace.GetDefaultFolder(olFolderSentMail) Set obj_OL = Nothing Set outMail = Nothing End Sub
– Brian Chew
Nov 1 at 6:25
See the updated answer above
– Dmitry Streblechenko
Nov 1 at 6:35
thank you dmitry. I tried and there is error with the set objItems =Application.Session.GetDefaultFolder(olFolderSentMail).Items said that it is invalid outside procedure. Where should I put it? should be inside my subprocedure in the module?
– Brian Chew
Nov 1 at 7:07
|
show 21 more comments
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
0
down vote
You can listen for the Items.ItemAdd event on the Sent Items folder (you can retrieve it using Namespace.GetDEfaultFolder(olFolderSentMail)).
EDIT: off the top of my head:
public WithEvents objItems As Outlook.Items
set objItems = Application.Session.GetDefaultFolder(olFolderSentMail).Items
...
Private objItems_ItemAdd(ByVal Item As Object)
Item.SaveAs Emailpath
End Sub
answered Oct 31 at 22:20
Dmitry Streblechenko
41.7k32760
Hi @dmitry, Thank you for replying. I don't really get what do you mean by listen for it. Any codes that I need to change. Sorry that I kind of new with Class Module.
– Brian Chew
Nov 1 at 2:11
Declare a global Items "with events" (like you do with obj_OL) variable and provide the ItemAdd event handler. Items collection comes from MAPIFolder.Items, and MAPIFolder is retrieved from Namespace.GetDEfaultFolder(olFolderSentMail)
– Dmitry Streblechenko
Nov 1 at 2:18
I am really sorry I don't get what you mean. This is my attempt. I am really sorry because I dont have any background with this. Public WithEvents obj_OL As Outlook.Application Private Sub obj_OL_ItemAdd(ByVal Item As Object, Cancel As Boolean) Item.ItemAdd Namespace.GetDefaultFolder(olFolderSentMail) Set obj_OL = Nothing Set outMail = Nothing End Sub
– Brian Chew
Nov 1 at 6:25
See the updated answer above
– Dmitry Streblechenko
Nov 1 at 6:35
thank you dmitry. I tried and there is error with the set objItems =Application.Session.GetDefaultFolder(olFolderSentMail).Items said that it is invalid outside procedure. Where should I put it? should be inside my subprocedure in the module?
– Brian Chew
Nov 1 at 7:07
|
show 21 more comments
up vote
0
down vote
You can listen for the Items.ItemAdd event on the Sent Items folder (you can retrieve it using Namespace.GetDEfaultFolder(olFolderSentMail)).
EDIT: off the top of my head:
public WithEvents objItems As Outlook.Items
set objItems = Application.Session.GetDefaultFolder(olFolderSentMail).Items
...
Private objItems_ItemAdd(ByVal Item As Object)
Item.SaveAs Emailpath
End Sub
answered Oct 31 at 22:20
Dmitry Streblechenko
41.7k32760
Hi @dmitry, Thank you for replying. I don't really get what do you mean by listen for it. Any codes that I need to change. Sorry that I kind of new with Class Module.
– Brian Chew
Nov 1 at 2:11
Declare a global Items "with events" (like you do with obj_OL) variable and provide the ItemAdd event handler. Items collection comes from MAPIFolder.Items, and MAPIFolder is retrieved from Namespace.GetDEfaultFolder(olFolderSentMail)
– Dmitry Streblechenko
Nov 1 at 2:18
I am really sorry I don't get what you mean. This is my attempt. I am really sorry because I dont have any background with this. Public WithEvents obj_OL As Outlook.Application Private Sub obj_OL_ItemAdd(ByVal Item As Object, Cancel As Boolean) Item.ItemAdd Namespace.GetDefaultFolder(olFolderSentMail) Set obj_OL = Nothing Set outMail = Nothing End Sub
– Brian Chew
Nov 1 at 6:25
See the updated answer above
– Dmitry Streblechenko
Nov 1 at 6:35
thank you dmitry. I tried and there is error with the set objItems =Application.Session.GetDefaultFolder(olFolderSentMail).Items said that it is invalid outside procedure. Where should I put it? should be inside my subprocedure in the module?
– Brian Chew
Nov 1 at 7:07
|
show 21 more comments
up vote
0
down vote
up vote
0
down vote
You can listen for the Items.ItemAdd event on the Sent Items folder (you can retrieve it using Namespace.GetDEfaultFolder(olFolderSentMail)).
EDIT: off the top of my head:
public WithEvents objItems As Outlook.Items
set objItems = Application.Session.GetDefaultFolder(olFolderSentMail).Items
...
Private objItems_ItemAdd(ByVal Item As Object)
Item.SaveAs Emailpath
End Sub
answered Oct 31 at 22:20
Dmitry Streblechenko
41.7k32760
You can listen for the Items.ItemAdd event on the Sent Items folder (you can retrieve it using Namespace.GetDEfaultFolder(olFolderSentMail)).
EDIT: off the top of my head:
public WithEvents objItems As Outlook.Items
set objItems = Application.Session.GetDefaultFolder(olFolderSentMail).Items
...
Private objItems_ItemAdd(ByVal Item As Object)
Item.SaveAs Emailpath
End Sub
answered Oct 31 at 22:20
Dmitry Streblechenko
41.7k32760
edited Nov 1 at 6:35
answered Oct 31 at 22:20
Dmitry Streblechenko
41.7k32760
answered Oct 31 at 22:20
Dmitry Streblechenko
41.7k32760
answered Oct 31 at 22:20
Dmitry Streblechenko
41.7k32760
41.7k32760
Hi @dmitry, Thank you for replying. I don't really get what do you mean by listen for it. Any codes that I need to change. Sorry that I kind of new with Class Module.
– Brian Chew
Nov 1 at 2:11
Declare a global Items "with events" (like you do with obj_OL) variable and provide the ItemAdd event handler. Items collection comes from MAPIFolder.Items, and MAPIFolder is retrieved from Namespace.GetDEfaultFolder(olFolderSentMail)
– Dmitry Streblechenko
Nov 1 at 2:18
I am really sorry I don't get what you mean. This is my attempt. I am really sorry because I dont have any background with this. Public WithEvents obj_OL As Outlook.Application Private Sub obj_OL_ItemAdd(ByVal Item As Object, Cancel As Boolean) Item.ItemAdd Namespace.GetDefaultFolder(olFolderSentMail) Set obj_OL = Nothing Set outMail = Nothing End Sub
– Brian Chew
Nov 1 at 6:25
See the updated answer above
– Dmitry Streblechenko
Nov 1 at 6:35
thank you dmitry. I tried and there is error with the set objItems =Application.Session.GetDefaultFolder(olFolderSentMail).Items said that it is invalid outside procedure. Where should I put it? should be inside my subprocedure in the module?
– Brian Chew
Nov 1 at 7:07
|
show 21 more comments
Hi @dmitry, Thank you for replying. I don't really get what do you mean by listen for it. Any codes that I need to change. Sorry that I kind of new with Class Module.
– Brian Chew
Nov 1 at 2:11
Declare a global Items "with events" (like you do with obj_OL) variable and provide the ItemAdd event handler. Items collection comes from MAPIFolder.Items, and MAPIFolder is retrieved from Namespace.GetDEfaultFolder(olFolderSentMail)
– Dmitry Streblechenko
Nov 1 at 2:18
I am really sorry I don't get what you mean. This is my attempt. I am really sorry because I dont have any background with this. Public WithEvents obj_OL As Outlook.Application Private Sub obj_OL_ItemAdd(ByVal Item As Object, Cancel As Boolean) Item.ItemAdd Namespace.GetDefaultFolder(olFolderSentMail) Set obj_OL = Nothing Set outMail = Nothing End Sub
– Brian Chew
Nov 1 at 6:25
See the updated answer above
– Dmitry Streblechenko
Nov 1 at 6:35
thank you dmitry. I tried and there is error with the set objItems =Application.Session.GetDefaultFolder(olFolderSentMail).Items said that it is invalid outside procedure. Where should I put it? should be inside my subprocedure in the module?
– Brian Chew
Nov 1 at 7:07
Hi @dmitry, Thank you for replying. I don't really get what do you mean by listen for it. Any codes that I need to change. Sorry that I kind of new with Class Module.
– Brian Chew
Nov 1 at 2:11
Hi @dmitry, Thank you for replying. I don't really get what do you mean by listen for it. Any codes that I need to change. Sorry that I kind of new with Class Module.
– Brian Chew
Nov 1 at 2:11
Declare a global Items "with events" (like you do with obj_OL) variable and provide the ItemAdd event handler. Items collection comes from MAPIFolder.Items, and MAPIFolder is retrieved from Namespace.GetDEfaultFolder(olFolderSentMail)
– Dmitry Streblechenko
Nov 1 at 2:18
Declare a global Items "with events" (like you do with obj_OL) variable and provide the ItemAdd event handler. Items collection comes from MAPIFolder.Items, and MAPIFolder is retrieved from Namespace.GetDEfaultFolder(olFolderSentMail)
– Dmitry Streblechenko
Nov 1 at 2:18
I am really sorry I don't get what you mean. This is my attempt. I am really sorry because I dont have any background with this. Public WithEvents obj_OL As Outlook.Application Private Sub obj_OL_ItemAdd(ByVal Item As Object, Cancel As Boolean) Item.ItemAdd Namespace.GetDefaultFolder(olFolderSentMail) Set obj_OL = Nothing Set outMail = Nothing End Sub
– Brian Chew
Nov 1 at 6:25
I am really sorry I don't get what you mean. This is my attempt. I am really sorry because I dont have any background with this. Public WithEvents obj_OL As Outlook.Application Private Sub obj_OL_ItemAdd(ByVal Item As Object, Cancel As Boolean) Item.ItemAdd Namespace.GetDefaultFolder(olFolderSentMail) Set obj_OL = Nothing Set outMail = Nothing End Sub
– Brian Chew
Nov 1 at 6:25
See the updated answer above
– Dmitry Streblechenko
Nov 1 at 6:35
See the updated answer above
– Dmitry Streblechenko
Nov 1 at 6:35
thank you dmitry. I tried and there is error with the set objItems =Application.Session.GetDefaultFolder(olFolderSentMail).Items said that it is invalid outside procedure. Where should I put it? should be inside my subprocedure in the module?
– Brian Chew
Nov 1 at 7:07
thank you dmitry. I tried and there is error with the set objItems =Application.Session.GetDefaultFolder(olFolderSentMail).Items said that it is invalid outside procedure. Where should I put it? should be inside my subprocedure in the module?
– Brian Chew
Nov 1 at 7:07
|
show 21 more comments
draft saved
draft discarded
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53075690%2fsave-email-as-msg-in-local-windows-folder-using-excel-vba%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
