Hi!
I'm a complete begginer, so i don't know much about coding.
I'm trying to create a button to run a macro that does the following:
1.- Save sheet2 in a specific location as PDF and saves the hyperlink in sheet24 of the active workbook in column 11
2.- Save a copy of some of the sheets of the active workbook in excel (macro enabled) in another specific location and save the hyperlink in sheet24 of the active workbook in column 12
3.- Send e-mail with PDF from sheet2 and put the date of when the mail was sent in sheet24
I managed to make work points 1 and 3 without problems, but with point 2 i'm not able to make it work, can anyone give me a heads up? Thanks
what i have that is working is this:
what i'm still trying to put inside and i have it aside because i can't figure out how to make it work is this:
With the following part i put it also on the sheet i want but creates another row, but i want to include it in the same row of the pdf, allthough different column and also i understand that i'm repeating some info that i already included before, but right now i have it in 2 separated macros.
I understand that if i want some concrete sheets i should do an array like:
sheets(array(sheet1,sheet2,sheet3,...)).copy -> but gives me error
If anyone can help i would appreciate it, thanks for your time and patience
I'm a complete begginer, so i don't know much about coding.
I'm trying to create a button to run a macro that does the following:
1.- Save sheet2 in a specific location as PDF and saves the hyperlink in sheet24 of the active workbook in column 11
2.- Save a copy of some of the sheets of the active workbook in excel (macro enabled) in another specific location and save the hyperlink in sheet24 of the active workbook in column 12
3.- Send e-mail with PDF from sheet2 and put the date of when the mail was sent in sheet24
I managed to make work points 1 and 3 without problems, but with point 2 i'm not able to make it work, can anyone give me a heads up? Thanks
what i have that is working is this:
VBA Code:
Sub saveaspdf()
Dim nºpresupuesto As Long
Dim nºpedido As String
Dim cliente As String
Dim importe As Currency
Dim fecha_presupuesto As Date
Dim path As String
Dim fname As String
Dim siguientepresupuesto As Range
nºpresupuesto = Range("J5")
nºpedido = Range("J4")
cliente = Range("B4")
importe = Range("N68")
fecha_presupuesto = Range("O4")
fname = nºpresupuesto & " - " & cliente
path = "C:\Users\.....\"
Sheet2.ExportAsFixedFormat Type:=xlTypePDF, IgnorePrintAreas:=False, Filename:=path & fname
Set siguientepresupuesto = Sheet24.Range("A1048576").End(xlUp).Offset(1, 0)
siguientepresupuesto = nºpresupuesto
siguientepresupuesto_Offset(0, 2) = cliente
siguientepresupuesto_Offset(0, 3) = importe
siguientepresupuesto_Offset(0, 4) = fecha_presupuesto
siguientepresupuesto_Offset(0, 13) = Now
Sheet2.Hyperlinks.Add anchor:=siguientepresupuesto_Offset(0, 12), Address:=path & fname & ".pdf"
Dim OutApp As Object
Dim OutMail As Object
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
With OutMail
.To = Range("B6")
.Subject = "Presupuesto nº " & nºpresupuesto
.Body = "Buenos días," & vbCrLf & "Nos complace enviarle, adjunto a este correo, en un archivo en PDF, el presupuesto solicitado." & vbCrLf & "Cualquier consulta estamos en contacto." & vbCrLf & "Atentamente,"
.Attachments.Add (path & fname & ".pdf")
.Display
End With
Set OutApp = Nothing
Set OutMail = Nothingwhat i'm still trying to put inside and i have it aside because i can't figure out how to make it work is this:
VBA Code:
Sub SavePedidoAsExcel()
Dim nºpresupuesto As Long
Dim nºpedido As String
Dim cliente As String
Dim importe As Currency
Dim fecha_presupuesto As Date
Dim path As String
Dim fname As String
nºpresupuesto = Range("J5")
nºpedido = Range("J4")
cliente = Range("B4")
importe = Range("N68")
fecha_presupuesto = Range("O4")
fname = nºpresupuesto & " - " & cliente
path = "C:\Users\.......\"
ActiveWorkbook.SaveCopyAs
With ActiveWorkbook
.SaveAs Filename:=path & fname, FileFormat:=51
.Close
End WithWith the following part i put it also on the sheet i want but creates another row, but i want to include it in the same row of the pdf, allthough different column and also i understand that i'm repeating some info that i already included before, but right now i have it in 2 separated macros.
VBA Code:
Set siguientepresupuesto = Sheet24.Range("A1048576").End(xlUp).Offset(1, 0)
siguientepresupuesto = nºpresupuesto
siguientepresupuesto_Offset(0, 2) = cliente
siguientepresupuesto_Offset(0, 3) = importe
siguientepresupuesto_Offset(0, 4) = fecha_presupuesto
Sheet2.Hyperlinks.Add anchor:=siguientepresupuesto_Offset(0, 11), Address:=path & fname & ".pdf"I understand that if i want some concrete sheets i should do an array like:
sheets(array(sheet1,sheet2,sheet3,...)).copy -> but gives me error
If anyone can help i would appreciate it, thanks for your time and patience
Last edited by a moderator:
