Macro Help required

[Arts]

Hi guys

I have the following code which was done by a former colleague

'
' Save Macro
Public Sub CommandButton1_Click()
ThisFile = Range("D18").Value
MyDir = Range("I1").Value
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=MyDir & ThisFile & ".pdf", Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
OpenAfterPublish:=True
End Sub

What the above does is create a pdf document and save it down to a location which works perfectly

Is it possible to build on this ?

What I would like to happen if possible is that I have an e mail address in cell A10 of the "Service Invoice" sheet

Would it be possible to attach the saved pdf and attach it to outlook and send it to the e mail address in cell A10

If also possible in the subject field of outlook once the email is aatched and ready to send is it possible to have the value in cell A5 of the "Service Invoice" Sheet and then the value in D18 which is the invoice number so the subject field reads "Palace Place, Inv No. 1003"

Any help on this would be most appreciated

 

[AlphaFrog]

Here's a good place to start.

Example Code for sending mail from Excel

 

[Arts]

Thanks Alphafrog

I shall take a look, is what I'm after seen as a big job or is it relatively simple, I only ask I have no macro experience whats so ever apart from knowing Alt+F11 opens the editor

 

[Arts]

I managed to find this code (online from a link from AlphaFrog) which was code to send a pdf as an e mail,

Function RDB_Mail_PDF_Outlook(FileNamePDF As String, StrTo As String, _<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
StrSubject As String, StrBody As String, Send As Boolean)<o></o>
Dim OutApp As Object<o></o>
Dim OutMail As Object<o></o>
<o> </o>
Set OutApp = CreateObject("Outlook.Application")<o></o>
Set OutMail = OutApp.CreateItem(0)<o></o>
<o> </o>
On Error Resume Next<o></o>
With OutMail<o></o>
.To = StrTo<o></o>
.CC = ""<o></o>
.BCC = ""<o></o>
.Subject = StrSubject<o></o>
.Body = StrBody<o></o>
.Attachments.Add FileNamePDF<o></o>
If Send = True Then<o></o>
.Send<o></o>
Else<o></o>
.Display<o></o>
End If<o></o>
End With<o></o>
On Error GoTo 0<o></o>
<o> </o>
Set OutMail = Nothing<o></o>
Set OutApp = Nothing<o></o>
End Function<o></o>

so i thought all i would do is paste below my previous code which saves it down as a pdf (earlier post 1.17pm) but wasnt as quite straight forward as that....

 

[Arts]

I have admitted defeat in this after hours of trying if anyone is able to give me a push in the right direction would be most helpful

 

[AlphaFrog]

You need to fill in the Email address, Subject text, and Body text in Red
This is not tested.

Public Sub CommandButton1_Click()

    Dim ThisFile As String, MyDir As String

    MyDir = Range("I1").Value
    ThisFile = MyDir & Range("D18").Value & ".pdf"
    
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisFile, _
                                    Quality:=xlQualityStandard, IncludeDocProperties:=True, _
                                    IgnorePrintAreas:=False, OpenAfterPublish:=True
                                    
    RDB_Mail_PDF_Outlook FileNamePDF:=ThisFile, _
                         StrTo:="[COLOR="Red"]Email@Blah.com[/COLOR]", _
                         StrSubject:="[COLOR="Red"]Subject of the email goes here[/COLOR]", _
                         StrBody:="[COLOR="Red"]Text for the body of the email" & vbLf & "goes here.[/COLOR]", _
                         Send:=True
End Sub

Function RDB_Mail_PDF_Outlook(FileNamePDF As String, StrTo As String, _
                              StrSubject As String, StrBody As String, Send As Boolean)
    Dim OutApp As Object
    Dim OutMail As Object

    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)

    On Error Resume Next
    With OutMail
        .To = StrTo
        .CC = ""
        .BCC = ""
        .Subject = StrSubject
        .Body = StrBody
        .Attachments.Add FileNamePDF
        If Send = True Then
            .Send
        Else
            .Display
        End If
    End With
    On Error GoTo 0

    Set OutMail = Nothing
    Set OutApp = Nothing
End Function

 

[Arts]

Thank you so much for that AlphaFrog...it works it works !!!! Lol so much relief right now I cant explain

Just a quick question would I need to fill in the e mail address each time myself

I have an e mail address in cell A10 is it possible to pick it up from there as the address is always changing

 

[AlphaFrog]

    RDB_Mail_PDF_Outlook FileNamePDF:=ThisFile, _
                         StrTo:=[COLOR="Red"]Range("A10").Value[/COLOR], _
                         StrSubject:="Subject of the email goes here", _
                         StrBody:="Text for the body of the email" & vbLf & "goes here.", _
                         Send:=True

 

[Arts]

Thank you so much works exactly how I would have liked it to

 

[Arts]

One minor minor point

On the body of the email i have put

StrBody:="Please find attached copy of invoice" & vbLf & "goes here.", _

On the body of the actual e mail it writes

Please find attched copy of invoice
goes here.


Is there any way i can get rid of the "goes here" part