VBA code to send Excel sheet in PDF format

Aretradeser

Board Regular
Joined
Jan 16, 2013
Messages
171
Office Version
  1. 2013
Platform
  1. Windows
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.
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

earthworm

Well-known Member
Joined
May 19, 2009
Messages
699
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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

Board Regular
Joined
Jan 16, 2013
Messages
171
Office Version
  1. 2013
Platform
  1. Windows
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

Well-known Member
Joined
May 19, 2009
Messages
699
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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

Board Regular
Joined
Jan 16, 2013
Messages
171
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

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
VBA Code:
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
VBA Code:
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
 
Solution

Aretradeser

Board Regular
Joined
Jan 16, 2013
Messages
171
Office Version
  1. 2013
Platform
  1. Windows
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,678
Messages
5,838,736
Members
430,566
Latest member
ChanchalSingh

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top