user-defined type not defined 2

[mirgss]

Hi:

I am writing some code to automatically send an Outlook email with information from an Access form. The test database works fine but when putting my code into the production database I get this error. I've seen advice on enabling the DAO Object Library; however I am not an administrator on my computer so I can't enable it. When I try to enable it from someone's account who is an admin, I get the "name conflicts with existing module, project or object library." I'm not sure this is even the problem, as I can get it to work on my machine without the DAO object library.

The error is at:
Dim objOutlook As outlook.Application

I don't program; everything I've done here is from Google and a little bit of tech knowledge. So any advice I probably need dumbed-down. Any help is GREATLY appreciated!

Regards,
Miranda

P.S. I'm so sorry - it looks like I have to paste the code right in the post.


Private Sub Box46_Click()
Dim email As String
Dim origin As String
Dim ref As String
Dim notes As String
Dim strBody As String
Dim strBody2 As String
Dim workOrder As Variant
Dim Building As String
Dim (a bunch of facility managers) as String


‘ right here I define all the facility managers with their emails

Dim objOutlook As outlook.Application
Dim objEmail As outlook.MailItem

email = Me!email
ref = Me!ref
notes = Me!notes
If Me!workOrder = notNull Then
workOrder = Me!workOrder
Else
workOrder = "N/A"
End If
If Me!Building = notNull Then
Building = Me!Building
Else
Building = "N/A"
End If

' Resolve each Recipient's name.

Set objOutlook = CreateObject("Outlook.application")
Set objEmail = objOutlook.CreateItem(outlookMailItem)
strBody = "Thank you for contacting DTMB Customer Service Center. Work order number " & workOrder & " has been created for your most recent request:" & vbNewLine & vbNewLine & "Request Details: " & notes
strBody2 = "If you have questions relating to this request, please reference the work order number when you contact the DTMB Customer Service Center at REDACTED." & vbNewLine & vbNewLine & "Thank you!"

With objEmail
.To = email
.SentOnBehalfOfName = "DTMB-customerservice@REDACTED"
.Subject = ref & "Request"
.Body = strBody & vbNewLine & vbNewLine & strBody2

' Add the CC recipient(s) to the message.
If Building = "Hannah Building" Or Building = "Ottawa Building" Or Building = "Hall of Justice" Then

.CC = FS

Else
If Building = "Constitution Hall" Or Building = "Van Wagoner Building" Or Building = "Williams Building" Then
.CC = FS

Else
If Building = "Austin Building" Or Building = "Joint Operations Center" Or Building = "MLHC" Or Building = "Mason Building" Then
.CC = FS

Else
If Building = "Cass Building" Or Building = "Romney Building" Or Building = "Lottery Building" Then
.CC = FS

Else
If Building = "MSP HQ" Or Building = "Grand Tower Building" Or Building = "Joint Lab" Then
.CC = FS
Else
If Building = "Cadillac Place" Then
.CC = FS
Else
If Building = "Energy Center" Or Building = "HAZMAT" Or Building = "MDOT Photo Lab" Or Building = "General Services Building" Or Building = "MDOT Warehouse" Or Building = "Operations Center" Or Building = "Vehicle Travel Services" Then
.CC = FS

Else
If Building = "General Office Building" Or Building = "MSP Forensic Center" Or Building = "MDOT C&T" Or Building = "Secretary of State Building" Or Building = "MSP Training Academy" Or Building = "MSP 1st District HQ" Then
.CC = FS

Else
If Building = "Flint SOB" Or Building = "Saginaw SOB" Then
.CC = FS

Else
If Building = "Jackson SOB" Then
.CC = FS

Else
If Building = "Traverse City SOB" Then
.CC = FS

Else
If Building = "Escanaba SOB" Or Building = "Grand Rapids SOB" Then
.CC = FS
End If
End If
End If
End If
End If
End If
End If


End If
End If
End If
End If
End If

For Each objOutlookRecip In .Recipients
objOutlookRecip.Resolve
Next

.Display 'sends the email in Outlook. Change to DISPLAY if you want to be able to
'modify or see what you have created before sending the email
End With

End Sub

 

[Andrzejek]

Since you set the Object to Outlook.App here:

Set objOutlook = CreateObject("Outlook.application")
Set objEmail = objOutlook.CreateItem(outlookMailItem)

You may as well do this before:

Dim objOutlook As Object [green]'outlook.Application[/green]
Dim objEmail As Object [green]' outlook.MailItem[/green]

And why do you have all the If-Then-ElseIf statements (If Building = "whatever") if all of them end up with:
.CC = FS
?

Have fun.

---- Andy

 

[mirgss]

Hi,

Thanks for the speedy reply!

I have

Dim objOutlook As outlook.Application
Dim objEmail As outlook.MailItem

Sorry if you couldn't find it - like I said I'm not a pro!

I removed the variables from the If-Then-Else statements to protect the innocent (and the guilty )

 

[mirgss]

Okay, so I removed

Dim objOutlook as outlook.Application
Dim objEmail as outlook.MailItem

and replaced it with your code and it works. Can you explain why? I like to try to understand

Thank you so much!

 

[Andrzejek]

If you have a Reference set to Outlook in your application, it is called an early binding, and you can use:

Dim objOutlook As Outlook.Application

But you can also use late binding, no Reference to Outlook:

Dim objOutlook As Object
Set objOutlook = CreateObject("Outlook.application")

Early binding is better for coding and debugging since you will have all intelisence with objOutlook object, but late binding is better for running code since you do not need to worry about what version of Outlook you have loaded on the machine.

And about your If statements, consider moving to Select Case:

Select Case Building
    Case "Hannah Building", "Ottawa Building", "Hall of Justice"
        .CC = FS
    Case "MSP HQ", "Grand Tower Building","Joint Lab"
        .CC = XY
    ....
End Select

Well, I would not have all the Buildings hard coded in there, I would have a table somewhere.

Have fun.

---- Andy

 

[mirgss]

Okay this definitely helps. Thank you so much!

Can I ask one more question? We do actually have a buildings table...how do I reference it?

 

[Andrzejek]

Since this is "Microsoft: Access Modules (VBA Coding)" forum, I would assume that you are already coding all of the above in VBA in Access. Am I right?

If so, you must access some tables in your Access data base already, because why would you code in Access in the first place?

Therefore, if you access other tables, then use your Buildings table instead of your If-Then-ElsIf (or Select Case) instead.

Am I wrong with my logic here?

Have fun.

---- Andy

 

[MajP]

Assuming you store email data in a table somewhere.

Assume some table called tblBuilding has the following
BuildingID
emailTo
emailCC
otherFields...

....
dim strCC as string
dim strTo as string
dim strWhere as string
.....
strWhere = "Building = '" & me.building & "'"
'use a dlookup to get values from a table base
'on a criteria. See helpfile on dlookup

strCC = NZ(dlookup("emailCC","tblBuilding",strWhere),"")
strTo = NZ(dlookup("emailTo","tblBuilding",strWhere),"")
....

 

[mirgss]

Well, as far as I'm concerned, I'm using Access because that's what was there when I started working here

I will try referencing the buildings table and see if I can make that work.

Thank you both for all of your help...this has definitely been a learning experience!

Miranda

 

[MajP]

I do not think the question was "why you are using Access?" but a statement of "since you are coding in access, pull the data from the table the same way you already have." But I will assume that so far you have not pulled data directly from a table/query into code. If that is the case there are two ways: domain aggregate functions, or recordsets.

If you need to pull a single piece of information at a time from a table/query the easiest is a domain aggregate function:

http://msdn.microsoft.com/en-us/library/aa168244(v=office.10).aspx

If you need to pull multiple pieces of information and do complex things like move, find, sort, filter then you need to use either a DAO.recordset or an ADO.recordset. For any real manipulation of database data you need to use recordsets. Once you learn recordsets it really opens up what you can do in Access.

http://msdn.microsoft.com/en-us/library/bb177501(v=office.12).aspx

 

[mirgss]

I see I have a lot to learn. This is actually my first exposure to VBA so while I know the theory and logic, I don't know the specific syntax. This has definitely been extremely helpful. I will certainly read up on recordsets.

When I was getting my original error, one of the possible solutions was enabling the DAO Object Library. When I tried to do that, I got the error: "name conflicts with existing module, project, or object library." ....I have no idea what that means. How can I fix that, so I can actually use the library?

Thanks again for all your help!

Miranda

 

[MajP]

In 2007/2010 you do not need a readd a reference to DAO object library because it is already in ACE.

See link

http://hitechcoach.com/index.php?op...o-library-in-acess-2007-and-2010&catid=27:vba

 

[PHV]

my original error
The solution wasn't to reference the DAO library but the Microsoft Outlook x.0 Object Library ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[mirgss]

Oh, terrific. That's great info to have. Sorry to be asking all the dumb questions, but like I said I'm pretty new at this and some of the in-depth solutions confuse me even more.

I appreciate the time you've taken to help me out.

Thanks again!

Miranda

 

[Andrzejek]

Some people say that there are no 'dumb questions'

Some other people say that it is a lot easier to answer 'dumb questions' that to correct 'dumb mistakes'

Have fun.

---- Andy