Save workbook to Desktop and email

[HughT]

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

 

[HughT]

Thank you so much! Can the user specify the folder where they want to save it themselves when they save? It may be different for different users.
Sorry to be picky!

 

[VBE313]

Do you want that to go in their Documents folder?

 

[VBE313]

Sub HughT()

On Error GoTo errhandler

Dim subjectVal As String

Dim strPath As String

strPath = Environ("USERPROFILE") & "\Documents\"

Set OutApp = CreateObject("Outlook.Application")

Set OutMail = OutApp.CreateItem(0)

With Sheets("Sheet1") 'CHANGE THIS TO THE SHEET NAME

    subjectVal = .Range("N8").Value

    ActiveWorkbook.SaveAs strPath & 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

 

[HughT]

Absolutely brilliant! Thank you very much for your patience and hard work, this has made a huge difference!

 

[VBE313]

You are absolutely welcome! Let me know if you want to make any other changes or additions to the code! I will be more than happy to help!