Send Sheet via VBA

[matthewnsarah07]

I usually use Access and can send object easily enough with vba but Excel I'm not sure about

I want to write a macro for a command button on an excel sheet which needs to:

Prompt user to Save As - if this can be done
Email the sheet to a predefined email address
CC the Staff Member who's email is in Cell A13 if this can be done
Close the sheet down

Can anyone tell me how I would do this in vba

Thanks for your help

 

[DCrake]

Have you tried recording a macro to do this?

 

[matthewnsarah07]

Would the record method work for things like Save As, I couldn't seem to get it to email either

Any Ideas?

 

[matthewnsarah07]

Tried recording - will do the Save As - what can I changei n the vba so it prompts the user for a filename rather than just saving

 

[smiler44]

If the user has book1 on screen and uses Save As to save it as book2 what is on screen becomes book2 and any alterations then take place in book2 and not book1. ActiveWorkbook.SaveCopyAs Filename: "c:\temp\book2.xls" (I think this is the right code) avoids this. what is on screen remains as book1.

Are you able to predefine the workbook name the user must use? for example book1_Jan.xlsm, book1_feb.xlsm if you are then the code below may help. it saves book1 as book1_lastmonthsname.xlsm.
if it does not work let me know and I'll have another go

Sub savecopieas()
               
        Dim directoryfound As String
        Const errpathnotfound As Integer = 76
        Dim handleErr
        Dim pat As String  ' temp directory path
        Dim fle As String 'file to save
        
        pat =  "c:\temp"
                
        fle = "\book1" & " " & MonthName((Month(Date)) - 1) & ".xlsm"   'file to save
                
        strpathname = pat & fle  
      On Error GoTo 0
       
        directoryfound = Dir(strpathname, vbDirectory)
        If (Len(directoryfound)) = 0 Then
        ActiveWorkbook.SaveCopyAs Filename:=pat & fle
        Else
'if the file name already exists
       Dim x
        x = MsgBox(fle & " " & "already exists. Do you wish to over write it?", 36, "confirm")
      
        If x = 6 Then ' yes overwrite existing file
        ActiveWorkbook.SaveCopyAs Filename:=pat & fle
       Else
       MsgBox ("File not saved")
        End If
        End If
0
        
        
        End Sub
 
[\code]
 
smiler44