Hey everybody! I've got a three part question about my code (listed
below).
My project is an excel spreadsheet linked to a web page which contains
various information that is updated every half-hour. When the user
clicks a button on the spreadsheet, the info is copied and pasted into
a blank e-mail. As you can see, different cells are being copied
depending on what time it is. I'm using "select case" combined with
the "time" function to figure out which cells need to be copied. Is
there anything besides "select case" I could use here to condense and
simplify the code, while still keeping it readable?

Second, the method of opening a blank e-mail and formatting it how I
want that I am currently using only works for very simple e-mails. I
have another project I'm working on that has a much more complex,
pre-formatted e-mail that needs to be sent out. How can I get Excel to
open a pre-existing outlook template, instead of just a blank outlook
e-mail?

And last, is it possible to have strings of text pasted into the e-mail
I create, instead of just values from cells? When I try to copy a cell
with text in it, or set a variable of "string" type equal to the
contents of a specific cell or range of cells, it just pastes a numeric
value into the e-mail, instead of the actual string. What am I doing
wrong here?

Thanks for any help anyone can offer!

My complete code is listed below:


Private Sub CommandButton3_Click()

Dim TheTime As Date
Dim RightNow As Integer
Dim A_Or_P As String
Dim SvcLvl As Double
Dim CllCm As Double
Dim AhtCm As Double
Dim ServiceLevel As Integer
Dim CallCuma As Integer
Dim HandleCuma As Integer

'Obtain only the base hour, instead of exact time with minutes and
seconds
TheTime = Time
RightNow = Hour(TheTime)

' Check to see if it is AM or PM
If RightNow < 12 Then A_Or_P = "AM" Else A_Or_P = "PM"

' Set time to 12 hour instead of 24 hour clock
Select Case RightNow
Case 13: RightNow = 1
Case 14: RightNow = 2
Case 15: RightNow = 3
Case 16: RightNow = 4
Case 17: RightNow = 5
Case 18: RightNow = 6
Case 19: RightNow = 7
Case 20: RightNow = 8
Case 21: RightNow = 9
Case 22: RightNow = 10
End Select

Application.EnableEvents = False
Application.ScreenUpdating = False

Range("Y40").Select
Selection.QueryTable.Refresh BackgroundQuery:=False

Select Case RightNow
Case 7: If A_Or_P = "AM" Then SvcLvl = Range("AC56").Value * 100
Else SvcLvl = Range("AC80").Value * 100
Case 8: If A_Or_P = "AM" Then SvcLvl = Range("AC58").Value * 100
Else SvcLvl = Range("AC82").Value * 100
Case 9: If A_Or_P = "AM" Then SvcLvl = Range("AC60").Value * 100
Else SvcLvl = Range("AC84").Value * 100
Case 10: SvcLvl = Range("AC62").Value * 100
Case 11: SvcLvl = Range("AC64").Value * 100
Case 12: SvcLvl = Range("AC66").Value * 100
Case 1: SvcLvl = Range("AC68").Value * 100
Case 2: SvcLvl = Range("AC70").Value * 100
Case 3: SvcLvl = Range("AC72").Value * 100
Case 4: SvcLvl = Range("AC74").Value * 100
Case 5: SvcLvl = Range("AC76").Value * 100
Case 6: SvcLvl = Range("AC78").Value * 100
End Select

Select Case RightNow
Case 7: If A_Or_P = "AM" Then CllCm = Range("AM56").Value * 100
Else CllCm = Range("AM80").Value * 100
Case 8: If A_Or_P = "AM" Then CllCm = Range("AM58").Value * 100
Else CllCm = Range("AM82").Value * 100
Case 9: If A_Or_P = "AM" Then CllCm = Range("AM60").Value * 100
Else CllCm = Range("AM84").Value * 100
Case 10: CllCm = Range("AM62").Value * 100
Case 11: CllCm = Range("AM64").Value * 100
Case 12: CllCm = Range("AM66").Value * 100
Case 1: CllCm = Range("AM68").Value * 100
Case 2: CllCm = Range("AM70").Value * 100
Case 3: CllCm = Range("AM72").Value * 100
Case 4: CllCm = Range("AM74").Value * 100
Case 5: CllCm = Range("AM76").Value * 100
Case 6: CllCm = Range("AM78").Value * 100
End Select

Select Case RightNow
Case 7: If A_Or_P = "AM" Then AhtCm = Range("AT56").Value * 100
Else AhtCm = Range("AT80").Value * 100
Case 8: If A_Or_P = "AM" Then AhtCm = Range("AT58").Value * 100
Else AhtCm = Range("AT82").Value * 100
Case 9: If A_Or_P = "AM" Then AhtCm = Range("AT60").Value * 100
Else AhtCm = Range("AT84").Value * 100
Case 10: AhtCm = Range("AT62").Value * 100
Case 11: AhtCm = Range("AT64").Value * 100
Case 12: AhtCm = Range("AT66").Value * 100
Case 1: AhtCm = Range("AT68").Value * 100
Case 2: AhtCm = Range("AT70").Value * 100
Case 3: AhtCm = Range("AT72").Value * 100
Case 4: AhtCm = Range("AT74").Value * 100
Case 5: AhtCm = Range("AT76").Value * 100
Case 6: AhtCm = Range("AT78").Value * 100
End Select

ServiceLevel = Fix(SvcLvl)
CallCuma = Fix(CllCm)
HandleCuma = Fix(AhtCm)

Range("Y40:AX91").Clear

Esubject = "DTV Cumulative Service Level as of " & RightNow & ":00
" & A_Or_P & " MST"
Sendto = "E-mail addresses here"
CCTo = "E-mail addresses here"
Ebody = "Cumulative Service Level - " & ServiceLevel & "%" & vbCr &
vbCr _
& "Cumulative Call Volume for the day - " & CallCuma & "%" & _
vbCr & vbCr & "Cumulative AHT for the day - " & HandleCuma & "%"

Set App = CreateObject("Outlook.Application")
Set Itm = App.CreateItem(0)
With Itm
..Subject = Esubject
..to = Sendto
..CC = CCTo
..body = Ebody
..display
End With

Set App = Nothing
Set Itm = Nothing

End Sub


--
xFiruath
------------------------------------------------------------------------
xFiruath's Profile: http://www.excelforum.com/member.php...o&userid=26528
View this thread: http://www.excelforum.com/showthread...hreadid=467011