Save workbook to Desktop and email

HughT

Board Regular
Joined
Jan 6, 2012
Messages
107
Office Version
365
Platform
Windows
I would be grateful if any help could be offered on this - I have searched about but could not find anything.

We have a macro enabled template called 'Referral' that we send out to agents. They complete this and save as a workbook and then email it back. The problem is that the naming convention is random and the email subject line is also random so that it is impossible to catalogue. What I would like to do is :

1. Save the work book to the agent's desktop using the filename in cell N8.
2. Then create and send an email to the office email address using the data in N8 as the subject line with the workbook attached to the email.
3. Code to be embedded in an onscreen button so that all the agent has to do is click it and it works automatically.

Apologies if this is a big ask.

Many thanks

HT
 

Some videos you may like

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

VBE313

Well-known Member
Joined
Mar 22, 2019
Messages
506
Office Version
365
Platform
Windows
In Visual Basic Editor (Alt + F11), go to Tools, References, and go check Microsoft Outlook 16.0 Object Library. After doing this, insert a Module and paste the following code, make sure you change the sheet name as i used "Sheet1" for example. After pasting this code, go to the Developer Ribbon in the excel application, under the "Controls" group go to "Insert", and insert a Form Control Button. and attach this button to the macro that you pasted. Let me know if you have any questions.
Snag_1375ca03.png


VBA Code:
Sub HughT()
On Error GoTo errhandler
Dim subjectVal As String
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
With Sheets("Sheet1") 'CHANGE THIS TO THE SHEET NAME
    subjectVal = .Range("N8").Value
    ThisWorkbook.SaveAs Filename:=subjectVal, _
    FileFormat:=xlOpenXMLWorkbookMacroEnabled
    With OutMail
        .To = "HughT@mrExcel.com"
        .CC = ""
        .BCC = ""
        .Subject = subjectVal
        .Attachments.Add ActiveWorkbook.FullName
        .Body = "Hi Hugh,"
        .Display
        '.Send      you can send the email without even looking at it
    End With
End With
Set OutMail = Nothing
Set OutApp = Nothing
Exit Sub
errhandler:
MsgBox "Contact Hugh"
End Sub
 

HughT

Board Regular
Joined
Jan 6, 2012
Messages
107
Office Version
365
Platform
Windows
Very many thanks, but I am afraid it didn't work (well the error message did!).
The file didn't save to the Desktop, and it didn't email. Help please?
 

VBE313

Well-known Member
Joined
Mar 22, 2019
Messages
506
Office Version
365
Platform
Windows
Please make sure that these two are checked off.
Snag_14414031.png


VBA Code:
Sub HughT()
'On Error GoTo errhandler
Dim subjectVal As String
Dim strPath As String
strPath = Environ("USERPROFILE") & "\Desktop\"
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
With Sheets("Sheet1") 'CHANGE THIS TO THE SHEET NAME
    subjectVal = .Range("N8").Value
Path = CreateObject("WScript.Shell").SpecialFolders("Desktop") & "\"
ActiveWorkbook.SaveAs Path & subjectVal
    With OutMail
        .To = "HughT@mrExcel.com"
        .CC = ""
        .BCC = ""
        .Subject = subjectVal
        .Attachments.Add ActiveWorkbook.FullName
        .Body = "Hi Hugh,"
        .Display
        '.Send      you can send the email without even looking at it
    End With
End With
Set OutMail = Nothing
Set OutApp = Nothing
'Exit Sub
'errhandler:
'MsgBox "Contact Hugh"
End Sub
 
Last edited:

HughT

Board Regular
Joined
Jan 6, 2012
Messages
107
Office Version
365
Platform
Windows
Thank you so much! This is genius!
 

HughT

Board Regular
Joined
Jan 6, 2012
Messages
107
Office Version
365
Platform
Windows
Got shouted at by the hierarchy! Apparently saving to the Desktop is a big no no because a file could sit there forever and not be found.
Is it possible therefore to format the file 'Save As' title as the contents of cell N8? (this contains the name, office and date, concatenated from data in the form) so that the user is presented with a predefined file name? I seem to recall that a previous version of Excel automatically defaulted the file name to whatever was in A1, but it doesn't seem to do that any more.
 

VBE313

Well-known Member
Joined
Mar 22, 2019
Messages
506
Office Version
365
Platform
Windows
Sorry I was a little confused with your response. Can you please show me an example of what the document name would be saved as?
 

HughT

Board Regular
Joined
Jan 6, 2012
Messages
107
Office Version
365
Platform
Windows
It would be the contents of N8 which will be the name, workplace and date. These are concatenated in N8 from three other cells on the form which the user inputs. The date is in dd-mmm-yy format so it doesn't contain any forward slashes (/).
The idea is that all worksheets would be saved in a folder and would automatically order themselves by name, workplace and date so we can identify who sent it, from where, and when. Unless we have a specified format, users simply put in whatever they like, which is usually not much!

Again, many thanks for your help.
 

VBE313

Well-known Member
Joined
Mar 22, 2019
Messages
506
Office Version
365
Platform
Windows
I changed the path route, you can update it accordingly so that you can specify the folder.

VBA Code:
Sub HughT()
'On Error GoTo errhandler
Dim subjectVal As String
Dim strPath As String
strPath = Environ("USERPROFILE") & "\Desktop\"
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
With Sheets("Sheet1") 'CHANGE THIS TO THE SHEET NAME
    subjectVal = .Range("N8").Value
Path = "C:\Users\HughT\Documents" & "\" 'HERE IS WHERE YOU SPECIFY THE FOLDER
ActiveWorkbook.SaveAs Path & subjectVal
    With OutMail
        .To = "HughT@mrExcel.com"
        .CC = ""
        .BCC = ""
        .Subject = subjectVal
        .Attachments.Add ActiveWorkbook.FullName
        .Body = "Hi Hugh,"
        .Display
        '.Send      you can send the email without even looking at it
    End With
End With
Set OutMail = Nothing
Set OutApp = Nothing
'Exit Sub
'errhandler:
'MsgBox "Contact Hugh"
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,095,168
Messages
5,442,796
Members
405,197
Latest member
queryashish

This Week's Hot Topics

  • Copy entire row if CountA <>0 to another sheet
    [B]I want to copy entire row if CountA <>0 for column J7:AM7 (headers on J6:AM6) and so on till the last used cell is column D and paste the...
  • Select last used Row in Table
    I have created a Table in a Worksheet which is locked to prevent user errors and protect formula. Some of the cells require freetext entries which...
  • excel workbook: do not allow certain file name
    Hello all, Don't think this has ever been asked before, but how do I restrict file save [Before_Save Event] if the name of the file being saved...
  • fixing problem autofilter
    hello i need help about my code when i search by code in textbox it doesn't show anything this is my data [ATTACH type="full"...
  • “Weight”
    Hi, i’ve got a long sheet filled with weights such as kg,g,L & ml. i can build a formula to convert kg into g and liter into ml. How ever, my...
  • How to capitalize everything before a certain character?
    In column A, I have some text: Hello good day.mp3 Hello good day.flac etc. I'd like to capitalize everything before the period. I don't need the...
Top