If within a sub problem

[gmc2k2]

Hi all,

I have an issue with code for a button that sends an email:

Private Sub Command41_Click()
Dim OApp As Object, OMail As Object, signature As String

Set OApp = CreateObject("Outlook.Application")
Set OMail = OApp.CreateItem(0)
With OMail
.Display
End With
signature = OMail.HTMLBody
With OMail
.To = Forms!Customer!EmailAddress
.Subject = Reference: " & REFERENCENUMBER
.Attachments.Add ("C:\Help\Test.txt")
.HTMLBody = strEmail & signature
'.Send
End With
Set OMail = Nothing
Set OApp = Nothing

Me.email sent = 1

End Sub

the strEmail has loads of text (which i've not pasted due to length) but i have 2 different types of emails to send depending on values on the record...

i'm trying to write some code so the .htmlbody is strbody1 or strbody2 depending on values on the record.

something like StrEmail = If (field 1 = "test" or "test2") then strbody1
else
If (field 1 = "test3" or "test4") then strbody2
End IF

But i'm having problems working this out?

any help would be appreciated

 

[pr2-eugin]

gmc2k2, check out.

Private Sub Command41_Click()
    Dim OApp As Object, OMail As Object
    [B][COLOR=Red]Dim strBody As String[/COLOR][/B]

    Set OApp = CreateObject("Outlook.Application")
    Set OMail = OApp.CreateItem(0)
    
   [COLOR=Red][B] Select Case Forms!Customer!yourFieldToTest
        Case "Test1", "Test2"[/B][/COLOR]
            [COLOR=Red][B]strBody =[/B][/COLOR] [COLOR=Blue][B]"Next time please use CODE tags, not QUOTE !"[/B][/COLOR]
        [COLOR=Red][B]Case "Test3", "Test4"[/B][/COLOR]
            [COLOR=Red][B]strBody =[/B][/COLOR] [COLOR=Blue][B]"I have performed a small clean up to your code"[/B][/COLOR]
        [COLOR=Red][B]Case Else[/B][/COLOR]
            [COLOR=Red][B]strBody =[/B][/COLOR] [COLOR=Blue][B]"If you have many criteria, use SELECT case over IF !"[/B][/COLOR]
    [COLOR=Red][B]End Select[/B][/COLOR]
    
    With OMail    
        .Display
        .To = Forms!Customer!EmailAddress
        .Subject = Reference: " & REFERENCENUMBER
        .Attachments.Add ("C:\Help\Test.txt")
        .HTMLBody = str[COLOR=Red][B]Body[/B][/COLOR] & .HTMLBody
        [COLOR=Green]'.Send[/COLOR]
    End With
    
    Set OMail = Nothing
    Set OApp = Nothing

    Me.email sent = 1
End Sub

 

[gmc2k2]

Paul you continuously help me out, i can't thank you enough buddy thanks again, i don't know why i selected quote...slip of the finger i think

 

[pr2-eugin]

Paul you continuously help me out, i can't thank you enough buddy thanks again, i don't know why i selected quote...slip of the finger i think

It's alright ! Glad to help.

 

[gmc2k2]

another quick question paul,

Ive adapted the code slightly to return a message box with yes and no buttons, what i would like is to email the details if the user is unregistered and they click yes on the messagebox to email me,

Private Sub openpipeline_Click()
Dim strEmailreg As String
Dim userStr As String
Dim oApp As Outlook.Application
Dim oMail As MailItem
Dim LResponse As Integer

LResponse = MsgBox("You are not registered, would you like to email details to register?", vbYesNo, "Registration")

If LResponse = vbYes Then
Set oApp = CreateObject("Outlook.application")

Set oMail = oApp.CreateItem(olMailItem)
oMail.Body = "Body of the email"
oMail.Subject = "Test Subject"
oMail.To = "Someone@somewhere.com"
oMail.Send
Else
End If

userStr = Nz(DLookup("Group", "tblUsers", "Username =""" & fOSUserName & """"), "No Rights")
Select Case userStr
Case "Admin"
DoCmd.OpenForm "Customer", acNormal
Case "User"
DoCmd.OpenForm "Customer", acNormal
Case Else
LResponse
End Select

Set oMail = Nothing
Set oApp = Nothing
End Sub

but it's returning a expected a Sub, function or property message, where am i going wrong? any help AGAIN would be appreciated

 

[pr2-eugin]

Okay you have done it again, please make your code more readable by using the CODE tag. So much better that way !

You have your logic confused. If the user has no rights, you will ask for them to register. Or am I not getting what you are coding? If you want to give the option for someone to send an email to someone if they would like to register, you need to modify as shown.

Private Sub openpipeline_Click()
    Dim userStr As String
    Dim oApp As Outlook.Application
    Dim oMail As MailItem

    userStr = Nz(DLookup("Group", "tblUsers", "Username =""" & fOSUserName & """"), "No Rights")
    
    Select Case userStr
        [COLOR=Red][B]Case "Admin", "User"[/B][/COLOR]
            DoCmd.OpenForm "Customer", acNormal
        [COLOR=Red][B]Case Else[/B][/COLOR]
            [COLOR=Red][B]If MsgBox("You are not registered, would you like to email details to register?", vbYesNo, "Registration") = vbYes Then[/B][/COLOR]
                Set oApp = CreateObject("Outlook.application")
                Set oMail = oApp.CreateItem(olMailItem)
                oMail.Body = "Body of the email"
                oMail.Subject = "Test Subject"
                oMail.To = "Someone@somewhere.com"
                oMail.Send
                Set oMail = Nothing
                Set oApp = Nothing
            [COLOR=Red][B]End If[/B][/COLOR]
    End Select
End Sub

 

[gmc2k2]

thank you so much again Paul, i'm sure i selected the code tag?? i double checked it this time too? it's been a very long day...apologies.

i've adapted it to use the fosusername function:

Private Sub openpipeline_Click()
    Dim userStr As String
    Dim oApp As Outlook.Application
    Dim oMail As MailItem
    userStr = Nz(DLookup("Group", "tblUsers", "Username =""" & fOSUserName & """"), "No Rights")
    
    Select Case userStr
        Case "Admin", "User"
            DoCmd.OpenForm "Customer", acNormal
        Case Else
            If MsgBox("You are not registered, would you like to email details to register?", vbYesNo, "Registration") = vbYes Then
                Set oApp = CreateObject("Outlook.application")
                Set oMail = oApp.CreateItem(olMailItem)
                oMail.Body = "Windows Login ID: " & fOSUserName()
                oMail.Subject = "Registration"
                oMail.To = "[EMAIL="someone@somehwere.com"]someone@somehwere.com[/EMAIL]"
                oMail.Display
            
                Set oMail = Nothing
                Set oApp = Nothing
            End If
    End Select
End Sub

This now works perfect i wanted it to run on click of opening a form button, so it checks if they are registered and if they are not registered then it will email me their details to register if that makes sense?

thank you again

 

[pr2-eugin]

That is what the code will do, glad that its working ! Good luck !