Multi Email idea for you

[gibbo1715]

Got tasked with making something at work and came up with the below that I think is quite useful.

It is simple but thought id post it here in case anyone else can benifit from the same idea

Basically I have a list of names in column A, i double click the name to generate an EMAIL about that person, that email is different dependant on the content of column E, The recipient name is in Column F. it also date stamps when the message was created as well in column G

I ve hard coded the body text of email message into my code here as thats what i needed

Hope someone finds this useful, if you think its worthy of a KB entry let me know and i ll submit it in the appropriate way

Cheers

Gibbo

***Remember to set a reference to outlook***


Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'Variable declaration
Dim oApp As Object, _
oMail As Object
Dim Name As String
Dim Action As String
Dim Failedon As String
Dim RecipientsName As String
Dim Bodytext As String
' Remember to set a reference to the Microsoft Outlook Object
'Error Handling
On Error GoTo Err:
'Check active column is column 1 (Where you want the macro to be called from)
If ActiveCell.Column = 1 Then
Name = ActiveCell.Value
Action = ActiveCell.Offset(0, 1).Value
Details = ActiveCell.Offset(0, 2).Value
RecipientsName = ActiveCell.Offset(0, 5).Value
Select Case EmailMessage
Case ActiveCell.Offset(0, 4).Value = "With Me"
Bodytext = " This is Email 1"
Case ActiveCell.Offset(0, 4).Value = "With You"
Bodytext = " This is Email 2"
Case ActiveCell.Offset(0, 4).Value = ""
Exit Sub
End Select
'Create and show the outlook mail item 1
Set oApp = CreateObject("Outlook.Application")
Set oMail = oApp.CreateItem(0)
With oMail
'Mail To
.To = RecipientsName
'Subject Text
.Subject = "Ref: " & Name & " Action: " & Action
'Body Text
.Body = Bodytext
.Importance = olImportanceHigh
.ReadReceiptRequested = True
'use .Display to show the mail
.Display
End With
Set oMail = Nothing
Set oApp = Nothing
End If
Exit Sub
Err:
MsgBox "Sorry there has been an error, please contact an administrator"
Set oMail = Nothing
Set oApp = Nothing
End Sub

[MWE]

I have not checked to see if a similar KB has already been submitted (you should do that). Assuming nothing similar, this would make a good KB. It certainly has good utility. Also, the process of submitting the KB will encourage you to review the proc's strucutre, comments, discription, etc.; all of which will generally improve the procedure.

If nothing else, you should post a thread in the area for potential KBs

[gibbo1715]

I ve put it forward as a KB entry

Cheers

Gibbo

[Norie]

gibbo

Nice code but do you need to repeat the code creating the email when the only difference is the body of the message?

[gibbo1715]

Umm Yes I agree Norie, i ve edited the code above so I dont repeat the code creating the email. If you want to improve it further please feel free

Cheers

Gibbo

[MOS MASTER]

Umm Yes I agree Norie, i ve edited the code above so I dont repeat the code creating the email. If you want to improve it further please feel free

Cheers

Gibbo

Hi Gibbo, :yes

Nice idea!

You've put this in there:
Remember to set a reference to the Microsoft Outlook Object

I have a problem with that cause your code is using both Early (Reference | Faster) and Late (Version independent object programming | Slower)

I like the idea making it all Late binding cause we already have a lot of early binding stuff and if you are developing platform independ Late binding is usually the way to go.

But I think you should just submit it and then we'll take it from there to get it approved.

Thanx for sharing! :whistle:

[gibbo1715]

I ve now submitted it for approval for the KB, I would like it to be as useful as possible so please feel free to change it as you see fit, i do like your idea of making it all late binding

cheers

gibbo

[MOS MASTER]

I ve now submitted it for approval for the KB, I would like it to be as useful as possible so please feel free to change it as you see fit, i do like your idea of making it all late binding

cheers

gibbo

Ok I'll contact you tomorrow to talk it over with you! :yes

[MOS MASTER]

Hi Gibbo, :yes

Can you put it up "For Approval"? It's still wipped (Work in progress) :whistle:

[gibbo1715]

sorry have now put it up for approval

[MOS MASTER]

sorry have now put it up for approval

No problem it's there I'll work on it tomorrow!

Thanx. :thumb

[Norie]

Gibbo

Your edit on the code looks good.

But just a pedantic thing on my part but you have declared all your variables apart from Details.

If anybody is using Option Explicit then that might cause compile errors.

[Ivan F Moala]

Gibbo

Your edit on the code looks good.

But just a pedantic thing on my part but you have declared all your variables apart from Details.

If anybody is using Option Explicit then that might cause compile errors.

Every one should use Option Explicit :)