• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

VBA required to auto save excel file on current users desktop and open an email with file attached in one step|VBA & Macros|Excel Forum|My Online Training Hub

You are here: Home / VBA required to auto save excel file on current users desktop and open an email with file attached in one step|VBA & Macros|Excel Forum|My Online Training Hub
Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumVBA & MacrosVBA required to auto save excel fil…
sp_PrintTopic sp_TopicIcon
VBA required to auto save excel file on current users desktop and open an email with file attached in one step
Avatar
monish saini

Active Member
Members
Level 0
Forum Posts: 4
Member Since:
February 12, 2020
sp_UserOfflineSmall Offline
1
February 12, 2020 - 8:24 pm
sp_Permalink sp_Print
Hello all,
 
Just joined the forum and wondering if you would be so kind enough to assist me with the following challenge.
 
I've an excel form and managed to have a macro created that attaches the file to an email when I click on a particular field. The macro is as below. I need to expand the macro so that it saves the file 1st to any user's desktop (not within any folder or the macro could perhaps create a temporary folder automatically if this required), then opens an email with the file attached.
 
I'd really appreciate your assistance as it is very challenging trying to find a particular solution via internet search.
 
Macro so far
Sub Low_Priority_Mail_workbook_Outlook()
 
Dim OutApp As Object
Dim OutMail As Object
 
Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)
 
On Error Resume Next
    With OutMail
        .to = "monish@yahoo.co.uk"
        .CC = ""
        .BCC = ""
        .Subject = "Form"
        .Body = "Hi," & vbNewLine & vbNewLine & "Please look into this request." & vbNewLine & vbNewLine & "Thanks"
        .Attachments.Add ActiveWorkbook.FullName
        .Display
   
End With
    On Error GoTo 0
 
    Set OutMail = Nothing
    Set OutApp = Nothing
    Set objMsg = Nothing
 

End Sub

 
*******************************************************************************************************
Form layout
The macros are linked to the "Submit" buttons.
 
QAAAABJRU5ErkJggg==Image Enlarger
Avatar
monish saini

Active Member
Members
Level 0
Forum Posts: 4
Member Since:
February 12, 2020
sp_UserOfflineSmall Offline
2
February 12, 2020 - 8:32 pm
sp_Permalink sp_Print

Please find file attached to this query

Avatar
Velouria
London or thereabouts
Moderator
Members


Trusted Members

Moderators
Level 4
Forum Posts: 613
Member Since:
November 1, 2018
sp_UserOfflineSmall Offline
3
February 12, 2020 - 8:50 pm
sp_Permalink sp_Print

You can get the desktop path using:

 

createobject("Wscript.Shell").specialfolders("desktop")

 

so you could use:

 

dim savedFile as string

savedfile = createobject("Wscript.Shell").specialfolders("desktop") & application.pathseparator & activeworkbook.name

activeworkbook.savecopyas savedfile

 

then later use savedFile in the .attachments.add line instead of activeworkbook.fullname

Avatar
monish saini

Active Member
Members
Level 0
Forum Posts: 4
Member Since:
February 12, 2020
sp_UserOfflineSmall Offline
4
February 13, 2020 - 12:01 am
sp_Permalink sp_Print

Thank you Velouria, this worked fine. Am I correct in saying this would work for any user who is working on the file?

I also want the file to be saved including current date e.g.  Support_Ticket_Form 12Feb20.xlsm

The original file name is Support_Ticket_Form.xlsm so I'd like to add the current date each time before a file is submitted by email otherwise the latest one will update the previous one and user will loose the history of requests submitted.

What would be the VBA addition for this?

Avatar
monish saini

Active Member
Members
Level 0
Forum Posts: 4
Member Since:
February 12, 2020
sp_UserOfflineSmall Offline
5
February 13, 2020 - 1:08 am
sp_Permalink sp_Print sp_EditHistory

I'm running into an issue as the file is saved originally to desktop with VBA. So next time user opens the same file VBA is trying to override it while it is still open and get the Run-Time error '1004' as per attachment.Capture.JPGImage Enlarger

I'm now thinking may be it is best not to save the file to desktop and once email is sent the file should be removed from desktop.

The email just needs to have the file attached after data already is entered in the file so it doesn't come blank after submission.

Any suggestions?

The VBA so far is as below:

 

Sub High_Priority_Mail_workbook_Outlook()

Dim savedFile As String

savedFile = CreateObject("Wscript.Shell").specialfolders("desktop") & Application.PathSeparator & ActiveWorkbook.Name
ActiveWorkbook.SaveCopyAs savedFile

Dim OutApp As Object
Dim OutMail As Object

Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)

On Error Resume Next
With OutMail
.to = "monish@yahoo.co.uk"
.CC = ""
.BCC = ""
.Subject = "Material Support Ticket Form - High Priority"
.Body = "Hi team," & vbNewLine & vbNewLine & "Please look into this request." & vbNewLine & vbNewLine & "Thanks"
.Attachments.Add savedFile

.Display

Application.DisplayAlerts = False
Application.ActiveWindow.Close SaveChanges:=False

End With
On Error GoTo 0

Set OutMail = Nothing
Set OutApp = Nothing
Set objMsg = Nothing

End Sub

sp_PlupAttachments Attachments
  • sp_PlupImage Capture.JPG (20 KB)
Avatar
Ben13

Active Member
Members
Level 0
Forum Posts: 4
Member Since:
August 25, 2016
sp_UserOfflineSmall Offline
6
February 13, 2020 - 4:46 pm
sp_Permalink sp_Print

Hi Monish,

Using ActiveWorkbook can get messy when renaming, saving, and deleting files.

I'd also recommend using a yyyymmdd date format if you want to be able to sort files easily in chronological order.

Plus - does the email recipient need to run macros? If not, I'd leave the VBA out of the attachment.

Further, saving to the user's desktop is fine if you're going to remove the file straightaway, but I'd use their temp environment just to make sure that the file is never sitting right in front of them tempting them to open it.

 

savedFile = Environ("temp") & "\Support_Ticket_Form_" & format(date, "yyyymmdd") & ".xlsx"

 

You can then delete the file from the user's temp after it's attached to the email:

 

Kill savedFile

 

Does this line...

    Application.ActiveWindow.Close SaveChanges:=False

...close the file containing the VBA? If so, you should probably get rid of it.

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Brian Pham, baber Tufail
Guest(s) 9
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 870
Purfleet: 412
Frans Visser: 346
David_Ng: 306
lea cohen: 219
A.Maurizio: 202
Jessica Stewart: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
drsven
Annie Witbrod
wahab tunde
Cong Le Duc
Faisal Bashir
Ivica Cvetkovski
Blaine Cox
Shankar Srinivasan
riyepa fdgf
Hannah Cave
Forum Stats:
Groups: 3
Forums: 24
Topics: 6205
Posts: 27211

 

Member Stats:
Guest Posters: 49
Members: 31880
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.