save in PDF and SEND the same by outlook

[Ebin425]

Dear Team,

Can any body explain how to save the active sheet name with current date and windows user name. Also explain how to call the
same in outlook.

Private Sub CommandButton1_Click()
ChDir "C:\TEMP"
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"C:\TEMP\ebin\Project-log.pdf", OpenAfterPublish:=True

Dim OutLookApp As Object
Dim OutLookMailItem As Object
Dim myAttachments As Object


Set OutLookApp = CreateObject("OutLook.application")
Set OutLookMailItem = OutLookApp.CreateItem(0)
Set myAttachments = OutLookMailItem.Attachments


With OutLookMailItem
.To = "ejoy@ccel.ae"


.Subject = "New Catalogue Request"
.Body = "New Catalogue Request"
myAttachments.Add "C:\TEMP\ebin\Project-log.pdf"


'.Send
.Display
End With


Set OutLookMailItem = Nothing
Set OutLookApp = Nothing


End Sub

 

[bugmonsta]

I have this code - its not mine and its something I "searched" for a while ago for one of my team that needed it, then I edited to fit our needs:


The code below will save the file as "firstname.lastname ddmmmyyy.pdf" (it includes the space between name and date) - easily editable on line 11

I included the OpenAfterPublish:=True as you had that in your initial code, (Line 37 in the code below)

This code will ask you to overwrite a file if it already exists, however on line 58 I have a "Kill the file once its attached" command. I have stopped this line from running, but I think its quite nice to kill the file as the person sometimes runs it more than once and it was annoying them to have the prompt! (plus it keeps the folder location tidy - I have matched your output to c:\temp)

I have not included the email address and display the email...hope this gets you going in the right direction.

Sub PDFitSendit()
Dim sht As Worksheet
Dim FFile As String
Dim dateme As String
Dim AskMeYesorNo As Integer
Dim OutlookObj As Object
Dim EmailObj As Object
Dim UsedRng As Range
Set sht = ActiveSheet
dateme = Now()
FFile = "c:\temp\" & Environ("UserName") & " " & Format(Date, "ddmmmyyyy") & ".pdf"

'Check if file already exist
If Len(Dir(FFile)) > 0 Then
AskMeYesorNo = MsgBox(Folder & " already exists." & vbCrLf & vbCrLf & "Do you want to overwrite it (say yes!)", _
vbYesNo + vbQuestion, "File Exists")
On Error Resume Next


If AskMeYesorNo = vbYes Then
Kill FFile
Else
MsgBox "Yo!, I need to overwrite this file to create the new one, to attach!." _
& vbCrLf & vbCrLf & "Press OK to exit this macro.", vbCritical, "Quiting!"
Exit Sub
End If
If Err.Number <> 0 Then
MsgBox "Unable to delete existing file.  Please make sure the file is not open or write protected." _
& vbCrLf & vbCrLf & "Press OK to exit this macro.", vbCritical, "Unable to Delete File"
Exit Sub
End If
End If
Set UsedRng = sht.UsedRange
If Application.WorksheetFunction.CountA(UsedRng.Cells) <> 0 Then

'Save as PDF file
sht.ExportAsFixedFormat Type:=xlTypePDF, Filename:=FFile, Quality:=xlQualityStandard, OpenAfterPublish:=True
'the above includes open after publish as shown in your code

'Create Outlook email
Set OutlookObj = CreateObject("Outlook.Application")
Set EmailObj = OutlookObj.CreateItem(0)
With EmailObj
.Display
.To = ""
.CC = ""
.Subject = sht.Name + ".pdf"
.Attachments.Add FFile
If DisplayEmail = False Then
        '.Send
End If
End With
Else
MsgBox "The active worksheet cannot be blank"
Exit Sub
End If

'Kill Ffile 'removes the file once its attached to the email so you dont get prompted each time!
End Sub