Macro for mailing read-only excel with selected cells

[Krishna20]

Hi,

I have an excel where I would like to mail selected cells (A1:C5) in the attachment.
I would like to mail it as read only. I am using Excel 2007, and I tried sending this using vba but had some difficulties
Can we send the selected area as PDF that is password protected. Can it be accessed using vba?
Thanks

Kittu20

 

[PeterB]

Hi Kittu,

I have presumed that you have Outlook on your computer (otherwise you are limited to just emailing the entire workbook) and created a solution which will email pdf files (based on the email list on your worksheet).

As far as I am aware, you cannot password protect a pdf from Excel. If that is critical then you could amend the solution to email a password protected workbook instead.

Sub EmailPDF()

Dim OutApp As Object
Dim OutMail As Object
Dim vaEmailList As Variant
Dim j As Long
Dim sCustomerName As String, sCustomerEmail As String
Dim TempFileName As String


On Error GoTo CleanUp

Set OutApp = CreateObject("Outlook.Application")

With Application
  .EnableEvents = True
  .ScreenUpdating = True
End With

vaEmailList = ThisWorkbook.Sheets(1).Range("rngEmailList").Value

For j = LBound(vaEmailList, 1) To UBound(vaEmailList, 1)

  sCustomerName = vaEmailList(j, 1)
  sCustomerEmail = vaEmailList(j, 2)
    
  'Create a file name
  TempFileName = ThisWorkbook.Path & "\" & sCustomerName & ".pdf"
  'Export to pdf file
  ThisWorkbook.Sheets(1).ExportAsFixedFormat Type:=xlTypePDF, Filename:=TempFileName, Quality:=xlQualityStandard, _
    IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
  'Save, Mail, Close and Delete the file
  Set OutMail = OutApp.CreateItem(0)
    On Error Resume Next
      With OutMail
        .to = sCustomerEmail
        .Subject = "Report"
        .Attachments.Add TempFileName
        .Body = "Body of email"
        .Display  'Or use Send
      End With
    On Error GoTo 0
  Set OutMail = Nothing

Next j



CleanUp:
  Set OutApp = Nothing

With Application
  .EnableEvents = True
  .ScreenUpdating = True
End With

End Sub

Regards,

Peter

 

[pecoflyer]

There is also a nice add-in by Ron de Bruin that does the job

 

[Krishna20]

Thanks Peter for your help.
Well I have done some search and I haven't found out anything related to password protected PDF (for Adobe) using Excel.