Edit Macro - Email document

CraigFord · Sep 7, 2011

i have created the macro below that on my PC does what i want, however when i try and do the smae from a different pc (with a different outlook account) it does not work, is there an easy fix somebody can recomend

if possable on the save document and the emaild document i would like the file name to end  "For authorisation"

Sub For_Auth()

'
' For_Auth Macro
'

'
ChDir "C:\Users\craigford\Desktop"
ActiveWorkbook.SaveAs Filename:= _
ThisWorkbook.Path & "\" & Cells(5, 5).Value & ".xls", _
FileFormat:=xlExcel8, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
Application.Dialogs(xlDialogSendMail).Show

End Sub

Andrew Poulsom · Sep 7, 2011

In what way doesn't it work? If the ChDir line is causing an error remove it, as it appears to be redundant.

CraigFord · Sep 7, 2011

Andrew Poulsom said:
In what way doesn't it work? If the ChDir line is causing an error remove it, as it appears to be redundant.

perfect, so simple!

i have seen it done but how can i add additonal words into the filename, after what had been selected for E5 i want it to say For authorisation

Andrew Poulsom · Sep 7, 2011

Like this?

Rich (BB code):

ActiveWorkbook.SaveAs Filename:= _
ThisWorkbook.Path & "\" & Cells(5, 5).Value & " For Authorisation.xls", _
FileFormat:=xlExcel8, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

CraigFord · Sep 7, 2011

thanks iv got this working perfectly now,

is there a way to protect the document from being Saved, i want people to be able to make as many changes as they want but only allow them to save as

Andrew Poulsom · Sep 7, 2011

Try this in the module for ThisWorkbook:

Code:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    If SaveAsUI = False Then
        MsgBox "Please use SaveAs."
        Cancel = True
    End If
End Sub

Or you could just convert your workbook into a template. Then when the user choose File|New and selects your template, a copy will be opened.

CraigFord · Sep 7, 2011

Andrew Poulsom said:
Try this in the module for ThisWorkbook:

Code:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) If SaveAsUI = False Then MsgBox "Please use SaveAs." Cancel = True End If End Sub

Or you could just convert your workbook into a template. Then when the user choose File|New and selects your template, a copy will be opened.

saving it as a template would be the best solution however when i do this the macros that save the document into the same file dont work.

im sure they save it somewhere but i dontknow where yet!

Edit Macro - Email document

CraigFord

New Member

Excel Facts

Andrew Poulsom

MrExcel MVP

CraigFord

New Member

Andrew Poulsom

MrExcel MVP

CraigFord

New Member

Andrew Poulsom

MrExcel MVP

CraigFord

New Member

Similar threads

Forum statistics

Share this page

Edit Macro - Email document

CraigFord

New Member

Excel Facts

Andrew Poulsom

MrExcel MVP

CraigFord

New Member

Andrew Poulsom

MrExcel MVP

CraigFord

New Member

Andrew Poulsom

MrExcel MVP

CraigFord

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock