VBA code to send Excel sheet in PDF format

[Aretradeser]

I need, by means of a VBA code, to send, from an Excel sheet, this same sheet, with the following conditions:
* That I can put the address of sending the email through a Msgbox.
* That the subject of the mail takes the name of the excel sheet to send.
* That the body of the email has the following text: "Enclosed I send file". The font of this text must be Calibri and size 14. In addition, an image must be included below the text: "Enclosed I send file", which is located in the path: C:\Documents\Images\Image1.png.
That the Excel sheet, in PDF format, that we are going to send by mail, complies with the following specifications:
* Horizontal format
* Left and right margins 1cm
* Top margin 2,5cm
* Lower margin 1cm
Finally, that it is not sent automatically, but that I am the one who sends the e-mail, once it has been opened in Outlook.

 

[earthworm]

It can be done . but that is too difficult to build vba without real data .

You need to paste row data and one sample result data
morever you also need to create a db containing emails to whom the file will be sent from specific folder . trust me i did it and its working fine.
but that requires lot of effort you need to create a db sheet containing all the information like a brain from where vba will run

 

[Aretradeser]

It can be done . but that is too difficult to build vba without real data .

You need to paste row data and one sample result data
morever you also need to create a db containing emails to whom the file will be sent from specific folder . trust me i did it and its working fine.
but that requires lot of effort you need to create a db sheet containing all the information like a brain from where vba will run

Thank you for your quick and attentive response. I don't think it would be necessary to create a database containing emails, as this would be done manually, via a Msgbox.

 

[earthworm]

Thank you for your quick and attentive response. I don't think it would be necessary to create a database containing emails, as this would be done manually, via a Msgbox.

Why do you want to re enter each time if you can automate the same completely on just one single click. Trust me I have build the code whereby data is sent to 52 different receipts along with 52 people in cc.all predefined

 

[Aretradeser]

Why do you want to re enter each time if you can automate the same completely on just one single click. Trust me I have build the code whereby data is sent to 52 different receipts along with 52 people in cc.all predefined

I am currently using these two codes and they work quite well. I only need to add the following to Code 1:
* The font of the text must be Calibri and size 14.
* An image must be included below the text: "Enclosed I am sending file", which can be found in the path: C:\Documents\Images\Image1.png.
That the PDF sent by e-mail has the following format:
* Horizontal format
* Left and right margins 1cm
* Top margin 2,5cm
* Lower margin 1cm
CODE 1

Sub EnvioEmail_HojaActiva_comoPDF()
Dim olApp As Object
Dim olMail As Object
Dim RutaTemporal As String, NombreFicheroTemporal  As String, RutaCompleta As String

'We disable the screen refresh
'and very important the events!
With Application
    .ScreenUpdating = False
    .EnableEvents = False
End With
 
'We define a variable that will be the Path where we will save,
''before sending as an attachment, the pdf that we will generate...
RutaTemporal = Environ$("temp") & "\"
 
'Generamos el nombre del fichero temporal .Pdf
NombreFicheroTemporal = ActiveSheet.Name & ".pdf"
 
'Combining the two previous variables, we will have the FullPath of our .pdf file.
RutaCompleta = RutaTemporal & NombreFicheroTemporal
 
'We debug possible errors at the time of exporting
'to the previous path, the active sheet as PDF.
On Error GoTo err
ActiveSheet.ExportAsFixedFormat _
        Type:=xlTypePDF, _
        Filename:=RutaCompleta, _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, _
        OpenAfterPublish:=False
 
'It's time to open the Outlook application.
Set olApp = CreateObject("Outlook.Application")

'and generate a new email to send...
Set olMail = olApp.CreateItem(0)
 
Dim destinatario As String, Asunto As String, Cuerpo As String
'THESE VARIABLES STILL NEED TO BE ASSIGNED VALUES!!!!
On Error Resume Next
With olMail
    .To = "miguel.cicuendez@polcia.es"                  'we add the recipient, the To...
    '.CC = destinatario                                 'to attach recipient in With Copy to...
    '.BCC = destinatario                                'to attach recipient in With Hard Copy to....
    .Subject = "Servicio de tardes"                     'we would indicate the Subject
    .Body = "Adjunto te remito el archivo de referencia" 'we would indicate the Body of the email
    'Attach the pdf file from the path where we saved it
    .Attachments.Add RutaCompleta
    Call EnvioFirma
    .Display    'or we will use .Send to send directly...
    '.Send
End With
On Error GoTo 0
 
'Now that the email has been sent (or displayed) with the pdf attached
'we can delete the pdf we had saved (in the temporary folder)...
Kill RutaCompleta
 
'We clear the created variables.
Set olMail = Nothing
Set olApp = Nothing
 
'Reset the previous conditions
'refresh the screen and trigger the events
With Application
    .ScreenUpdating = True
    .EnableEvents = True
End With
 
Exit Sub

'for error checking in case of exporting as Pdf...
err:
    MsgBox err.Description
 
End Sub

CODE 2

Sub EnvioFirma()
    Dim a As Worksheet, b As Worksheet
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    On Error Resume Next
    ChDir (ActiveWorkbook.Path)
    Firma = Application.GetOpenFilename("Archivos Excel (*.pn*), *.pn*")
    If VarType(Firma) = vbBoolean Then
        MsgBox ("Operación cancelada"), vbCritical, "AVISO"""
        Exit Sub
    End If
    sini = "<P><FONT FACE= ""Calibri""><FONT SIZE=4 pto.>En archivo adjunto, te remito el servicio de tardes. <P></FONT>"
    stbl = "<Div> <IMG SRC=""" & Firma & """><br><br></Div>"
    ‘stbl = TableHTML
    sbdy = sini & vbNewLine & vbNewLine & vbNewLine & vbNewLine & vbNewLine
    sbdy = sbdy & vbNewLine & vbNewLine & stbl & vbNewLine & vbNewLine
    sbdy = sbdy & spie
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
End Sub

 

[Aretradeser]

Why do you want to re enter each time if you can automate the same completely on just one single click. Trust me I have build the code whereby data is sent to 52 different receipts along with 52 people in cc.all predefined

Thanks for your help, earthworm. I have solved the problem.

 

[dhruvthumar002]

Is any video tutorial available?