Emailing From Excel

[Chris Brown]

I have set up a macro that emails a sheet and retains the name of the
workbook in the subject line when Outlook is opened. I would like for it to
retain the name of the workbook, but I want to add the data from a particular
cell to the subject line as well.

Subject line example: NISR05 - Plastic Cup

Where "NISR05" is the name of the workbook and "Plastic Cup" is the data
located in cell B4.

Any help would be greatly appreciated!

 

[Ron de Bruin]

Hi Cris
Do you use sendMail or Outlook code

 

[Chris Brown]

I use sendMail. In fact, I found a macro that you wrote and am trying to
masage it for what I am doing (with some, but not total success).

Sub Mail_Every_Worksheet()
Dim sh As Worksheet
Dim wb As Workbook
Dim strdate As String
Application.ScreenUpdating = False
For Each sh In ThisWorkbook.Worksheets
If sh.Range("a1").Value Like "?*@?*.?*" Then
strdate = Format(Now, "dd-mm-yy h-mm-ss")
sh.Copy
Set wb = ActiveWorkbook
With wb
.SaveAs "Sheet " & sh.Name & " of " _
& ThisWorkbook.Name & " " & strdate & ".xls"
.SendMail ActiveSheet.Range("a1").Value, _
"NISR05" & " " & "-" & " " & sh.Range("b4")
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With
End If
Next sh
Application.ScreenUpdating = True
End Sub

I do not want to specify the email address - I would like for the user to
type it in.

 

[Ron de Bruin]

Hi Chris

Try this

..SendMail "", _
Left(ThisWorkbook.Name, Len(ThisWorkbook.Name) - 4) & " - " & sh.Range("b4").Value

 

[Chris Brown]

I'm still learning the VBA side. How do I load the code you provided? I
know I still need the Sub and End Sub - I just don't know where to put
...SendMail "", _
Left(ThisWorkbook.Name, Len(ThisWorkbook.Name) - 4) & " - " &
sh.Range("b4").Value

 

[Ron de Bruin]

Replace the sendmail line in the code example you posted with the new line I posted

so replace

.SendMail ActiveSheet.Range("a1").Value, _
"NISR05" & " " & "-" & " " & sh.Range("b4")


for

..SendMail "", _
Left(ThisWorkbook.Name, Len(ThisWorkbook.Name) - 4) & " - " & sh.Range("b4").Value

 

[Chris Brown]

Sorry - I figured it out right after I clicked Post.

Last question: How can I remove the Read Only command? If I delete the
line, the macro errors out.

 

[Ron de Bruin]

Why ?

Do you want to keep the file you send

 

[Chris Brown]

I use the sheet as an approval process where each person that receives the
file is able to approve with their name on an appropriate line and send to
the next. Security is not really an issue on this - it is really just a
means to eliminate paper. There is not a need to keep the file that is sent.
Users are able to access the sent file through Outlook.

 

[Ron de Bruin]

You can't remove the Read Only command because it is used to delete
the activeworkbook (WB in the code) when it is open.

The file you send is not Read Only

 

[Chris Brown]

I gotcha - you have been a tremendous help and I thank you for the time that
you have given me!

 

[Ron de Bruin]

You are welcome