Emailing workbook - subject line text

iandb

New Member
Joined
Feb 16, 2009
Messages
34
Office Version
  1. 365
Platform
  1. Windows
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"
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
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.


Code:
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
 
Upvote 0
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 :confused:

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
 
Upvote 0
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
 
Upvote 0
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
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,198
Members
448,554
Latest member
Gleisner2

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
Back
Top