Select Case Giving Me Errors! Code needs help?

[roxannep]

I have a Make Table Query that sets up a table wherein one field may have one of three options. Depending on the option, a particular email message needs to go out to them.

Below is the code (and I am new to this, so please bear with all the obvious errors), and I get a message back telling me it cannot find my field "|" and highlights the Select Case [AuctionCode] line.

Public Sub Shipping_Email_Confirmation_Click()
Dim db As Database, rst As Recordset
Dim a As Integer, EmailInfo As String

Set db = CurrentDb()
Set rst = db.OpenRecordset("tblEmailShipConfirmBP&quot

rst.MoveLast
rst.MoveFirst

For a = 1 To rst.RecordCount
' Infomation could also change here
Select Case [AuctionCode]

'emails for customers purchasing from Ebay
Case "E"
EmailInfo = "Shipping Confirmation for "
EmailInfo = EmailInfo & rst![Title] & ", "
EmailInfo = EmailInfo & "Ebay Confirmation No. " & rst![Item]
EmailInfo = EmailInfo & ". The product you purchased through E-Bay was shipped on " & rst![ShipDate]
EmailInfo = EmailInfo & " to "
EmailInfo = EmailInfo & rst![Name] & " at "
EmailInfo = EmailInfo & rst![Address] & ", "
EmailInfo = EmailInfo & rst![City] & ", " & rst![StateorProvince] & " " & rst![PostalCode] & "." & Chr$(10) & Chr$(13)
EmailInfo = EmailInfo & "" & Chr$(10) & Chr$(13)
EmailInfo = EmailInfo & "If any of this information is incorrect, please contact us at 360-699-1795. And thank you for your order!"

'emails for customers purchasing from Yahoo
Case "Y"
EmailInfo = "Shipping Confirmation for "
EmailInfo = EmailInfo & rst![Title] & ", "
EmailInfo = EmailInfo & "Yahoo Confirmation No. " & rst![Item]
EmailInfo = EmailInfo & ". The product you purchased through Yahoo was shipped on " & rst![ShipDate]
EmailInfo = EmailInfo & " to "
EmailInfo = EmailInfo & rst![Name] & " at "
EmailInfo = EmailInfo & rst![Address] & ", "
EmailInfo = EmailInfo & rst![City] & ", " & rst![StateorProvince] & " " & rst![PostalCode] & "." & Chr$(10) & Chr$(13)
EmailInfo = EmailInfo & "" & Chr$(10) & Chr$(13)
EmailInfo = EmailInfo & "If any of this information is incorrect, please contact us at 360-699-1795. And thank you for your order!"

'emails for customers purchasing from Amazon
Case "A"
EmailInfo = "Shipping Confirmation for "
EmailInfo = EmailInfo & rst![Title] & ", "
EmailInfo = EmailInfo & "Amazon Confirmation No. " & rst![Item]
EmailInfo = EmailInfo & ". The product you purchased through Amazon was shipped on " & rst![ShipDate]
EmailInfo = EmailInfo & " to "
EmailInfo = EmailInfo & rst![Name] & " at "
EmailInfo = EmailInfo & rst![Address] & ", "
EmailInfo = EmailInfo & rst![City] & ", " & rst![StateorProvince] & " " & rst![PostalCode] & "." & Chr$(10) & Chr$(13)
EmailInfo = EmailInfo & "" & Chr$(10) & Chr$(13)
EmailInfo = EmailInfo & "If any of this information is incorrect, please contact us at 360-699-1795. And thank you for your order!"
End Select

DoCmd.SendObject acSendNoObject, " ", acFormatTXT, rst!EmailName, , , "Shipping Confirmation", EmailInfo, False, " "

rst.MoveNext
Next
DoCmd.Close
End Sub

 

[Guest_imported]

select case rst!auctioncode

 

[MichaelRed]

The below may simplify maintenance of your code. Generally try to set up "boiler plate" to accept a variable (or even a few). Then use the logic just to set the variables.

Select Case [AuctionCode]

'emails for customers purchasing from whichever
Case "E"
MyDotCom = "Ebay"
Case "Y"
MyDotCom = "Yahoo"

Case "A"
MyDotCom = "Amazon"
End Select

EmailInfo = "Shipping Confirmation for "
EmailInfo = EmailInfo & rst![Title] & ", "
EmailInfo = EmailInfo & "MyDotCom & " Confirmation No. " & rst![Item]
EmailInfo = EmailInfo & ". The product you purchased through "& MyDotCom & " was shipped on " & rst![ShipDate]
EmailInfo = EmailInfo & " to "
EmailInfo = EmailInfo & rst![Name] & " at "
EmailInfo = EmailInfo & rst![Address] & ", "
EmailInfo = EmailInfo & rst![City] & ", " & rst![StateorProvince] & " " & rst![PostalCode] & "." & vbCrLf
EmailInfo = EmailInfo & & vbCrLf
EmailInfo = EmailInfo & "If any of this information is incorrect, please contact us at 360-699-1795

MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over

 

[roxannep]

I tried the suggestions above. The module does not hang up on the [AUctionCode] portion, THANK YOU, glo4, but it does hang up on the boilerplate.

It wants me to define MyDotCom. Need help in know where to place it.

Dim MyDotCom as ?

 

[MichaelRed]

Dim MyDotCom as String[tab][tab]'in this function


MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over