macro to save as

Microsoft Excel 2007 home and student
November 3, 2009 at 03:22:23
Specs: Windows XP
I would like to have a macro to run a save as
command using the contents of an excel cell as
the name. I have generated this cell by combining
3 cells. Icing on the cake if it could generate an
e-mail message.

See More: macro to save as

Report •

November 3, 2009 at 05:13:37

Here is macro code for saving the workbook using a generated name in Cell A4 on sheet1 of the active workbook:

Option Explicit

Private Sub TestSave()
Dim strName As String
strName = ActiveWorkbook.Worksheets("Sheet1").Range("A4").Text
ActiveWorkbook.SaveCopyAs Filename:=strName
End Sub

I included the intermediate step of creating the filename as a string so that you can single step through the macro and see the filename that will be used, or use it in an e-mail, but you can just use:



Report •

November 3, 2009 at 05:56:57

Here it is with email.

There are several e-mail settings that you will have to enter yourself, including the name of the SMTP server. You will likely have these settings already in your email program.

There is a *****RISK*****
This code includes your e-mail address and password.

I suggest that:
a. This code is in your Personal.xls file and NOT the workbook that you are saving, so that this information doesn't get sent on to others with the workbook
b. You use an Input box to request your password, rather than hardcode it in the Macro.
c. You might setup an e-mail just for this use, with a username and password separate from your own account.

Option Explicit

Private Sub TestSave()

Dim strName As String
Dim objEMail As Object
On Error GoTo ErrHnd

'get filename
strName = ActiveWorkbook.Worksheets("Sheet1").Range("A4").Text
'save a copy of the active workbook with the new filename
ActiveWorkbook.SaveCopyAs Filename:=ActiveWorkbook.Worksheets("Sheet1").Range("A4").Text

'create an e-mail object
Set objEMail = CreateObject("CDO.Message")
'create the e-mail fields
objEMail.From = "my name"
objEMail.To = "the e-mail address complete with @"
objEMail.Subject = "We've saved an Excel File"
objEMail.Textbody = strName
'setup the SMTP server
objEMail.Configuration.Fields.Item _
    ("") = 2
'Name of Remote SMTP Server
objEMail.Configuration.Fields.Item _
    ("") = "smtp. your email provider"
'Server port = 25 (typical - but check yours)
objEMail.Configuration.Fields.Item _
    ("") = 25
objEMail.Configuration.Fields.Item _
    ("") = "your email name including @"
objEMail.Configuration.Fields.Item _
("") = "your password"
'update the fields
'send the message
Set objEMail = Nothing
Exit Sub
'error handler
Set objEMail = Nothing
End Sub

Note: Underscore _ at the ends of some lines is the line continuation character and these are all one long line.

I am not an expert on e-mail, all I can say is that this works for me.

Others may have better or more secure methods.


Report •

Related Solutions

Ask Question