Repost for Ron

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jenny B. - 21 May 2008 02:23 GMT

Hi Ron,

I’m familiar with the Call Function and use it quite often, but that’s what
I’m trying to avoid in this paticular application. If you run the first
macro and Call the second – you get the send email prompt twice and that’s
what I’m looking to avoid. That’s why I’m looking to combine the two vs.
running them separately.

Any other ideas?

Thanks – Jenny B.

Simple add this as the last line in the first macro

Call SecondMacroName

Signature

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm

"Jenny B." <JennyB@discussions.microsoft.com> wrote in message
news:1B063E60-0D88-47F1-A420-5F80C6115F42@microsoft.com...
Good Afternoon,

I have a Macro I use to send an email to a group after a spreadsheet has
been completed. I’m looking to incorporate another send piece to the
existing routine and was wondering if someone could offer some advice.

The first email is programmed to always send just a message to particular
person after completion - Sub SalesandLisaEmail. The Second Macro (MySend2)
is set-up to send a copy of the Active Worksheet to a different person. I’m
looking to change this up so both run at the same time vs. each running one
at a time. I’m also looking to change the send to on the Second Macro and
have it send the copy to the User vs. the a static email address (i.e.,
.Value = Environ(" username").

Lastly, is there a way to write additional logic in the routine which will
remove the Macros from the Worksheet being sent to the user?

Thanks in advance for your review and thoughts – Jenny B.

Sub SalesandLisaEmail()

On Error GoTo IndicateError

Dim Dockwkbk As Workbook
Set Dockwkbk = Workbooks("Account Entry Database BETA.xls")

Set OLook = CreateObject("Outlook.Application")
Set Mitem = OLook.createitem(0)
Mitem.To = "Name@hotmail.com"
Mitem.Subject = "New Statement Checklist" & " - " &
Dockwkbk.Sheets("Account Mgmt Checklist").Range("F5").Value
Mitem.body = "A New Checklist has been created for review." & vbNewLine &
vbNewLine & "Please review and forward to the DMS Group when complete." _
& vbNewLine & vbNewLine & "Thank you - Account Management."

Mitem.SEND

Set OLook = Nothing
Set Mitem = Nothing

Exit Sub

IndicateError:
MsgBox "Your email message failed. Please select Finished again."

Resume Next

End Sub

Sub MySend2()

ThisWorkbook.Sheets("Account Mgmt Checklist").Copy
With ActiveWorkbook
.SendMail Recipients:="Username@centurytel.net", _
Subject:="Copy of Checklist" & " " & Range("F5").Value & " " &
Format(Date, "mm/dd/yy") .Close SaveChanges:=False
End With

End Sub

Did this post answer the question?

Why should

Reply to this Message

Bob Phillips - 21 May 2008 09:39 GMT

Surely that means the second macro has superfluous code, a send instruction?

Signature

---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

> Hi Ron,
>
[quoted text clipped - 78 lines]
>
> Why should

Reply to this Message