Ok i'm trying to put an Error control where if the activecell row returns an error, it will occupy a cell in the row and just write "Error" on it. The code works fine until i put the On Error Control. I think i need advise on the proper placement of the If else where the "Error" word will be put into. Below is the code.
我在尝试设置一个错误控件如果activecell行返回一个错误，它将占据行中的一个单元并在其上写入“Error”。代码运行良好，直到我设置了错误控制。我想我需要关于“如果”这个词的正确位置的建议。下面是代码。
Private Sub CommandButton1_Click()
Dim outlookapp As Outlook.Application
Dim outlookmail As Outlook.MailItem
Dim myusername As String
Dim LastRow As Long, CurRow As Long, DestRow As Long, DestLast As Long
Dim checkstatus As String
Dim ws1 As Worksheet, ws2 As Worksheet
Set ws1 = Sheets("Sheet1")
LastRow = ws1.Range("A" & Rows.Count).End(xlUp).Row
On Error Resume Next
For CurRow = 2 To LastRow
myusername = Environ("Username")
Set outlookapp = New Outlook.Application
Set outlookmail = outlookapp.CreateItemFromTemplate("C:\Users\" & myusername & "\AppData\Roaming\Microsoft\Templates\testtemplate.oft")
With outlookmail
.SentOnBehalfOfName = "SharedMailbox"
.To = ActiveCell.Cells(CurRow, 6)
.Subject = Replace(outlookmail.Subject, "xProjID", ActiveCell.Cells(CurRow, 1))
.Subject = Replace(outlookmail.Subject, "xProjName", ActiveCell.Cells(CurRow, 2))
.Subject = Replace(outlookmail.Subject, "xVert", ActiveCell.Cells(CurRow, 5))
.HTMLBody = Replace(outlookmail.HTMLBody, "xXName", ActiveCell.Cells(CurRow, 2))
.HTMLBody = Replace(outlookmail.HTMLBody, "xProjID", ActiveCell.Cells(CurRow, 1))
.HTMLBody = Replace(outlookmail.HTMLBody, "xStat", ActiveCell.Cells(CurRow, 10))
.HTMLBody = Replace(outlookmail.HTMLBody, "xManID", ActiveCell.Cells(CurRow, 6))
.HTMLBody = Replace(outlookmail.HTMLBody, "xName", ActiveCell.Cells(CurRow, 7))
End With
ActiveCell.Cells(CurRow, 11) = "Yes"
ActiveCell.Cells(CurRow, 12) = DateTime.Now
If Err.Number <> 0 Then
ActiveCell.Cells(CurRow, 13) = "Error"
End If
outlookmail.Send
Next CurRow
MsgBox "Mass Mailer Complete"
End Sub
So what this does is it gets the Outlook Alias of a particular user and send the email to him/her. So i tried to put erroneous Alias like "xxxx". The thing is the word "error" which is supposed to be on activecell.cells(currow,13) for the rows with "xxxx" gets logged with rows with the Correct Alias. so where should i put the code if err code?
它的作用是获取特定用户的Outlook别名并将邮件发送给他/她。所以我试着用错误的别名，比如“xxxx”。事情是“error”这个词应该在activecell.cells(currow,13)上，对于带有“xxxx”的行，会用正确的别名记录行。如果是err代码，我应该将代码放在哪里?
If Err.Number <> 0 Then
ActiveCell.Cells(CurRow, 13) = "Error"
End If
Thanks!
谢谢!
1 个解决方案
#1
1
In general, always try to make your code as simple as you can and eliminate the useless things, just to the things that do not work. You need something like this:
一般来说，总是尽量使代码尽可能简单，并消除无用的东西，只针对那些不能工作的东西。你需要这样的东西:
Option Explicit
Public Sub Test()
On Error Resume Next
Debug.Print 5 / 0
If Err.Number <> 0 Then
Cells(1, 1) = Err.Description
End If
On Error GoTo 0
End Sub
Then starting from there, you can build the code further. If you need a loop and Err.Clear as supposed in the comment from @Mat's Mug, it will look like this:
然后从这里开始，您可以进一步构建代码。如果你需要一个循环并犯错。正如@Mat的马克杯评论所言，它看起来是这样的:
Option Explicit
Public Sub Test()
Dim lngCounter As Long
Dim lngcounter2 As Long
On Error Resume Next
Cells.Clear
For lngCounter = 1 To 5
Debug.Print lngCounter / IIf(lngCounter Mod 2 = 0, 1, 0)
If Err.Number <> 0 Then
Cells(lngCounter, 1) = Err.Description
End If
Err.Clear
Next lngCounter
On Error GoTo 0
End Sub
This gives 3 errors in 5 iterations and writes their description in column A of the active excel worksheet.
这将在5次迭代中产生3个错误，并将它们的描述写入active excel工作表的A列。
#1
1
In general, always try to make your code as simple as you can and eliminate the useless things, just to the things that do not work. You need something like this:
一般来说，总是尽量使代码尽可能简单，并消除无用的东西，只针对那些不能工作的东西。你需要这样的东西:
Option Explicit
Public Sub Test()
On Error Resume Next
Debug.Print 5 / 0
If Err.Number <> 0 Then
Cells(1, 1) = Err.Description
End If
On Error GoTo 0
End Sub
Then starting from there, you can build the code further. If you need a loop and Err.Clear as supposed in the comment from @Mat's Mug, it will look like this:
然后从这里开始，您可以进一步构建代码。如果你需要一个循环并犯错。正如@Mat的马克杯评论所言，它看起来是这样的:
Option Explicit
Public Sub Test()
Dim lngCounter As Long
Dim lngcounter2 As Long
On Error Resume Next
Cells.Clear
For lngCounter = 1 To 5
Debug.Print lngCounter / IIf(lngCounter Mod 2 = 0, 1, 0)
If Err.Number <> 0 Then
Cells(lngCounter, 1) = Err.Description
End If
Err.Clear
Next lngCounter
On Error GoTo 0
End Sub
This gives 3 errors in 5 iterations and writes their description in column A of the active excel worksheet.
这将在5次迭代中产生3个错误，并将它们的描述写入active excel工作表的A列。