PDA

View Full Version : Open outlook new email and attach most recent file from directory



lucass3231
07-14-2016, 02:50 AM
Dear all :)

I'm trying to create a vba macro that allows me to open new message in outlook and attach most recent xlsx file from my folder path. All after one button click.

any ideas how to do it? Thanks in advance! :)

mdmackillop
07-14-2016, 04:20 AM
Sub EmailWithOutlook()
'Variable declaration
Dim oApp As Object, _
oMail As Object, _
Pth As String

'Turn off screen updating
Application.ScreenUpdating = False

'Thanks to SNB
c00 = ActiveWorkbook.Path & "\"
Pth = Split(CreateObject("wscript.shell").exec("cmd /c dir """ & c00 & "*.xlsx"" /b/s/a-d/o-d").stdout.readall, vbCrLf)(0)



'Create and show the outlook mail item
Set oApp = CreateObject("Outlook.Application")
Set oMail = oApp.CreateItem(0)
With oMail
'Uncomment the line below to hard code a recipient
'.To = "someone@somedomain.com"
'Uncomment the line below to hard code a subject
'.Subject = "Look at my workbook!"
.Attachments.Add Pth
.Display
End With


'Restore screen updating and release Outlook
Application.ScreenUpdating = True
Set oMail = Nothing
Set oApp = Nothing
End Sub

lucass3231
07-14-2016, 04:30 AM
mdmackillop thanks for it, however i am not sure how it should work, especially those two lines:


c00 = ActiveWorkbook.Path & "\"
Pth = Split(CreateObject("wscript.shell").exec("cmd /c dir """ & c00 & "*.xlsx"" /b/s/a-d/o-d").stdout.readall, vbCrLf)(0)

and where i should put my folder path?

thanks anyway!

mdmackillop
07-14-2016, 05:07 AM
Change this line or add some other form of input

c00 = ActiveWorkbook.Path & "\"

lucass3231
07-14-2016, 05:29 AM
Ok, I changed this line to my folder path c00 = "C:\Users\ll59205\Desktop\New folder\komunikaty\"
But still I have a run time error: Subscript out of range and highlighed next line:

Pth = Split(CreateObject("wscript.shell").exec("cmd /c dir """ & c00 & "*.xlsx"" /b/s/a-d/o-d").stdout.readall, vbCrLf)(0)

I am not sure if you understand me correctly, but my active excel file path is different than that from where i want to attach this file.

mdmackillop
07-14-2016, 05:45 AM
This works for me. Check that there is a "xlsx" file in the folder.

c00 = "C:\TestDir\"
Pth = Split(CreateObject("wscript.shell").exec("cmd /c dir """ & c00 & "*.xlsx"" /b/s/a-d/o-d").stdout.readall, vbCrLf)(0)

lucass3231
07-14-2016, 06:52 AM
I restart excel and it starts working :) Thank you!

By the way, do you know if it will be possible to save this email after i click 'send' button in a folder on my disk?

mdmackillop
07-14-2016, 07:43 AM
I don't have outlook on this laptop. Have a look here (http://www.slipstick.com/developer/code-samples/save-selected-message-file/) or post in the Outlook forum.

lucass3231
07-15-2016, 03:54 AM
I have found something like this, however I'm not sure how to connect it with my previous part that open new mail and attach file. What do you think?


Sub Application_Startup()Dim objSent As Outlook.MAPIFolder
Set objNS = Application.GetNamespace("MAPI")
Set objSentItems = objNS.GetDefaultFolder(olFolderSentMail).Items
Set objNS = Nothing
End Sub


Sub objSentItems_ItemAdd(ByVal Item As Object)
Dim sPath As String
Dim dtDate As Date
Dim sName As String
Dim enviro As String

enviro = "C:\Users\ll59205\Desktop\New folder\archiwum"

sName = Item.Subject
ReplaceCharsForFileName sName, "-"

dtDate = Item.ReceivedTime
sName = Format(dtDate, "yyyymmdd", vbUseSystemDayOfWeek, _
vbUseSystem) & Format(dtDate, "-hhnnss", _
vbUseSystemDayOfWeek, vbUseSystem) & "-" & sName & ".msg"

sPath = enviro
Debug.Print sPath & sName
Item.SaveAs sPath & sName, olMSG
End Sub

mdmackillop
07-15-2016, 04:07 AM
Simplest way is to call both from a third macro

Sub DoStuff()
Call Macro1
Call Macro2
End Sub