Emailing workbook - subject line text

[iandb]

All,

Hoping someone can help.

I have a excel book with several sheets that get filled in and the aim is to have a button on the last sheet to send the completed form attached to an email. I've managed to get this bit sorted and a simple subject line with some generic text however I'd like to have specific text also added and also to different email addresses

Below is the macro I've done to send it to just me so far.

Sub SendIt()
Application.Dialogs(xlDialogSendMail).Show arg1:=Array("someone@somewhere.com"), _
arg2:="test report"
End Sub


I assume that after the first email I simply add another with in a new set of "" but would this be in the same bracket set?

Arg2:= "Test Report" is the subject and is a manual addition on the macro, however I would like (if at all possible) to added 2 bits of info from different cells with in the workbook that are on the same sheet.

For example if on sheet 1 in cell A2 I have "Bacon" and then in cell A15 I have "Sandwich" then I'm hoping to have the email subject "Test Report Bacon Sandwich"

 

[FormR]

Hi, something like this..

Sub SendIt()
 Application.Dialogs(xlDialogSendMail).Show arg1:=Array("someone@somewhere.com", "someonelse@somewhereelse.com"), _
 arg2:="Test report " & Range("A2").Value & " " & Range("A15").Value
End Sub

 

[Juaque]

Hi maybe this can help too. I found it a couple of years ago don't remember who posted it.

It's not as clean as the one from FormR but it can be adjusted.

Option Explicit
Sub Mail_File()

' Working in Office 2000-2013
    Dim OutApp As Object
    Dim OutMail As Object
    Dim strbody As String
    Dim To1 As String, CC As String
    Dim WB As Workbook
'    Dim MSGx As String

'    MSGx = MsgBox("Did you save the workbook before clicking E-mail ...?", vbYesNo)
    
'    If MSGx = vbYes Then
    Set WB = ActiveWorkbook
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)

    To1 = Sheets("X").Range("Y").Value 'I had my email addresses stored in cells and referred to them.
    CC = Sheets("X").Range("Z").Value
    
    strbody = "<p><font face=""Arial"" size=""3"" ><strong>Good day,</strong></p>" & _
              "<p><font face=""Arial"" size=""3"" >Please find attached the file as requested named: <span style=""color:#0000FF;""><strong>" & [I had a variable here] & "</strong></span>.</p>" & _
              "<font face=""Arial"" size=""3"" >" & "<B>Kind regards,</B>" _

    On Error Resume Next

    With OutMail
        .Display
        .To = To1
        .CC = CC
        .BCC = ""
        .Subject = "File Name: (" & OpName & ") - "
        .HTMLBody = strbody & "<br>" & .HTMLBody
        .Attachments.Add WB.FullName
'        .Send
    End With

    On Error GoTo 0
    Set OutMail = Nothing
    Set OutApp = Nothing
    
'    Else
'    MsgBox "Please save the file before mailing it.", vbInformation
'    End If

End Sub

 

[iandb]

Thanks for the speedy reply.

Not sure if its the hangover or just me not understanding fully. Can you kindly explain to a clown like me the subject part below

arg2:="Test report " & Range("A2").Value & " " & Range("A15").Value

Do I need to put the sheet name in? Does it matter that the email "button" is on sheet 9

The below give me a compile error? Assuming a basic error by me some where
arg2:="test report " & Sheet 1 ("C1").Value & " " & sheet 1 ("Q1").Value

Thanks again

 

[FormR]

Do I need to put the sheet name in? Does it matter that the email "button" is on sheet 9

If you don't then the active sheet is used. If you want to qualify the range with the sheet name, try like this:

arg2:= "Test report " & Sheets("Sheet1").Range("A2").Value & " " & Sheets("Sheet1").Range("A15").Value

 

[iandb]

Hi there

Thanks again and sorry for my complete ineptitude lol

The info on sheet 1 carrys through until sheet where ever the button is. So just simply changed the cells in your initial reply to the detail I needed and all fine

Greatly appreciated