Hi!
From MrExcel & PlanetPDF I have found some VBA-code that takes my print_area and uses the AcrobatDistiller to create a PDF-file and finally sends it with Lotus NOTes... but....
I tried to insert a msgbox at the beginning of the macro that ask the user if he is sure that he want to publish. My idea was that if he clicked on OK, the macro will perform - otherwise exit sub.
Quite simple I thought, but in one way or the other, the macro doesnt manage to attach the PDF-file to the Lotus NOTes that I send... and I really dont know why?
Could anybody (NateO ???) tell me whats wrong with my idea?
My code is as follows:
All the best... ShvDk
Edit: NPO added code tags.
From MrExcel & PlanetPDF I have found some VBA-code that takes my print_area and uses the AcrobatDistiller to create a PDF-file and finally sends it with Lotus NOTes... but....
I tried to insert a msgbox at the beginning of the macro that ask the user if he is sure that he want to publish. My idea was that if he clicked on OK, the macro will perform - otherwise exit sub.
Quite simple I thought, but in one way or the other, the macro doesnt manage to attach the PDF-file to the Lotus NOTes that I send... and I really dont know why?
Could anybody (NateO ???) tell me whats wrong with my idea?
My code is as follows:
Code:
Sub Save_file_CreatePDF_Publish_PDF_with_LotusNotes
Dim PSFileName As String
Dim PDFFileName As String
Dim DistillerCall As String
Dim ReturnValue As Variant
Dim Maildb As Object
Dim UserName As String
Dim MailDbName As String
Dim MailDoc As Object
Dim Session As Object
Dim EmbedObj As Object
Dim AttachME As Object
x = Application.ActivePrinter
Application.ActivePrinter = "Acrobat Distiller on Ne00:"
PSFileName = Range("filename_postscript").Value
PDFFileName = Range("filename_pdf").Value
If Dir(PSFileName) <> "" Then Kill (PSFileName)
If Dir(PDFFileName) <> "" Then Kill (PDFFileName)
SendKeys PSFileName & "{ENTER}", False
ActiveSheet.PrintOut , PrintToFile:=True
PSFileName = Chr(34) & PSFileName & Chr(34)
PDFFileName = Chr(34) & PDFFileName & Chr(34)
DistillerCall = "C:\Program Files\Adobe\Acrobat 4.0\Distillr\AcroDist.exe" _
& " /n /q /o" & PDFFileName & " " & PSFileName
ReturnValue = Shell(DistillerCall, vbNormalFocus)
If ReturnValue = 0 Then MsgBox "Creation of " & PDFFileName & "failed."
PSFileName = Range("filename_postscript").Value
If Dir(PSFileName) <> "" Then Kill (PSFileName)
Application.ActivePrinter = x ’returns to the former ActivePrinter
Set Session = CreateObject("Notes.NotesSession")
UserName = Session.UserName
MailDbName = Left$(UserName, 1) & Right$(UserName, (Len(UserName) - InStr(1, User-Name, " "))) & ".nsf"
Set Maildb = Session.GetDatabase("", MailDbName)
If Maildb.IsOpen = True Then
Else
Maildb.OPENMAIL
End If
Set MailDoc = Maildb.CREATEDOCUMENT
MailDoc.Form = "Memo"
MailDoc.sendto = Range("file_modtager").Value
MailDoc.Subject = Range("file_emne").Value
MailDoc.Body = "Denne mail er sendt direkte fra Excel...!!!!"
attachment1 = Range("filename_pdf").Value
If attachment1 <> "" Then
On Error Resume Next
Set AttachME = MailDoc.CREATERICHTEXTITEM("attachment1")
Set EmbedObj = AttachME.embedobject(1454, "attachment1", _
Range("filename_pdf").Value, "")
On Error Resume Next
End If
MailDoc.Send 0, Recipient
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs FileName:=Range("filename_excel").Value
Application.DisplayAlerts = True
Range("a1").Select
End SubAll the best... ShvDk
Edit: NPO added code tags.
