forums1167
07-19-2013, 11:10 PM
Hello, I am having trouble getting the below code to work. When I compile the code it says: User-defined type not defined. In this code, I am sending an email through excel. I am also using an outlook template file. Also, I have created a loop at the end. Any assistance is greatly appreciated. Thank you,
Private Declare Function ShellExecute Lib "shell32.dll" _
Alias "ShellExecuteA" (ByVal hwnd As Long, ByVal lpOperation As String, _
ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, _
ByVal nShowCmd As Long) As Long
Private Declare Function apiGetUserName Lib "advapi32.dll" Alias _
"GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long
Sub TicketResolved()
Dim myOlApp As Outlook.Application
Dim MyItem As Outlook.MailItem
On Error GoTo ErrorHandler1
Set myOlApp = CreateObject("Outlook.Application")
Set MyItem = myOlApp.CreateItemFromTemplate("C:\OPEN -ALL\TICKET RESOLVED\Ticket Resolved Template")
With MyItem
.To = Cells(3, 2) & "; forums1167@gmail.com"
.Subject = "Ticket Resolved #" & Cells(3, 1)
.HTMLBody = Replace(.HTMLBody, "Issue1", Cells(3, 1))
.HTMLBody = Replace(.HTMLBody, Lastfirstname1, Cells(3, 2))
.Display
End With
Set MyItem = Nothing
Set myOlApp = Nothing
Rows("3:3").Select
Selection.Delete Shift:=xlUp
While Not IsEmpty(Cells(3, 3))
Call TicketResolved
Wend
End Sub
Private Declare Function ShellExecute Lib "shell32.dll" _
Alias "ShellExecuteA" (ByVal hwnd As Long, ByVal lpOperation As String, _
ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, _
ByVal nShowCmd As Long) As Long
Private Declare Function apiGetUserName Lib "advapi32.dll" Alias _
"GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long
Sub TicketResolved()
Dim myOlApp As Outlook.Application
Dim MyItem As Outlook.MailItem
On Error GoTo ErrorHandler1
Set myOlApp = CreateObject("Outlook.Application")
Set MyItem = myOlApp.CreateItemFromTemplate("C:\OPEN -ALL\TICKET RESOLVED\Ticket Resolved Template")
With MyItem
.To = Cells(3, 2) & "; forums1167@gmail.com"
.Subject = "Ticket Resolved #" & Cells(3, 1)
.HTMLBody = Replace(.HTMLBody, "Issue1", Cells(3, 1))
.HTMLBody = Replace(.HTMLBody, Lastfirstname1, Cells(3, 2))
.Display
End With
Set MyItem = Nothing
Set myOlApp = Nothing
Rows("3:3").Select
Selection.Delete Shift:=xlUp
While Not IsEmpty(Cells(3, 3))
Call TicketResolved
Wend
End Sub