Excel 2016 Email VBA not working
I have some code, see below, that I have been using for 6-7 months in Excel 2010 to generate an email and add pdf attachments. My company switched to 2016 recently and the code stopped working, but I cannot figure out why --neither can IT or my peers. I do not have the most advance vba skills and would appreciate any advice given. The code runs all the way through in 2016, even when you go step by step, but does not generate an email to review. The ** part near the bottom is where I think something is going on, because when I comment that part out then the email will pop up--but without the additional pdf's attached. I opened the immediate window and name21 and attach21 populate correctly.
Sub Email23() Dim path21, date21, body21, to21, cc21, attach21, name21 As String Dim xOutlook As Object Dim xMailItem As Object Dim xRg As Range Dim xCell As Range Dim xEmailAddr As String Dim xTxt As String On Error Resume Next date21 = Month(Sheets("Data Entry Team").Range("C10").Value) & "_" & Year(Sheets("Data Entry Team").Range("C10").Value) path21 = "(folderpath removed in example)" & date21 & "\" & "Data Entry Team - " & date21 & ".pdf" body21 = Sheets("Named Tables").Range("AA7").Value 'to21 i = 0 For Each cell In Sheets("Named Tables").Range("U7:U11") If InStr(Sheets("Named Tables").Range("U7").Offset(i, 0).Value, "@") > 0 Then to21 = to21 & ";" & Sheets("Named Tables").Range("U7").Offset(i, 0).Value End If i = i + 1 Next cell 'cc21 i = 0 For Each cell1 In Sheets("Named Tables").Range("Y7:Y15") If InStr(Sheets("Named Tables").Range("Y7").Offset(i, 0).Value, "@") > 0 Then cc21 = cc21 & ";" & Sheets("Named Tables").Range("Y7").Offset(i, 0).Value End If i = i + 1 Next cell1 xTxt = ActiveWindow.RangeSelection.Address 'Unique code below for the email Set xOutlook = CreateObject("Outlook.Application") Set xMailItem = xOutlook.CreateItem(0) With xMailItem .To = to21 .CC = cc21 .Subject = "Subject" .body = body21 & vbNewLine & Signature .Attachments.Add path21 **i = 0 For Each attach1 In Sheets("Named Tables").Range("U12:U30") name21 = Sheets("Named Tables").Range("U12").Offset(i, 0).Value 'Debug.Print name21 attach21 = "(folderpath removed in example)" & date21 & "\Month in Review - " & name21 & ".pdf" 'Debug.Print attach21 .Attachments.Add attach21 i = i + 1 Next attach1** .display End With Set xOutlook = Nothing Set xMailItem = Nothing End Sub
See also questions close to this topic
C# Excel to Xml and convert it to .net object
Reading complex excel file content via c# is a bit hard for me. So I am saving as XML my excel file. So I want to read all xml content and convert it to .Net objects.
Is there a built in object type for read this xml cotent or is this possible?
Clicking dropdown arrow of Excel Data Validation entry no longer enters data into cell
So I have been following this video to prepare a data validation list that is able to auto-complete user entries: https://www.youtube.com/watch?v=vkPoViUhkxU
Essentially at about 10 mins, the instructor demonstrates that when the user types in a letter or part of a word into a data validation cell, when the drop-down of the data validation is clicked this produces a dynamic list of all possible entries.
I am able to produce a dynamic list, but the list is only created when I click off the cell not when the dropdown arrow is depressed as in the video. I was wondering if I am missing a setting here to allow data to be entered into a cell with the dropdown, not just on pressing the enter key?
Any thoughts or guidance would be greatly appreciated,
VBA Filter stuck on second row
I'm filtering on multiple sheets by using the following code:
The scenario is I'm copying data from one workbook and paste them on to the current workbook. Sheets (Table 1 to Table 13 S) pull data from the sheet with copied data through formulas. I have used the same code for other workbook with the same structured data, the only difference is that I copy different rows.
For some strange reason, in this workbook, from Table 1 to Table 13, the filers are stuck on the second row. However, for Table 1 S to Table 13 S the filters are all normal.
I'm pulling my hair out for this lol...
Set y = ActiveWorkbook y.Sheets("Table 1").Range("AA2").AutoFilter Field:=27, Criteria1:="x" y.Sheets("Table 2").Range("AB2").AutoFilter Field:=28, Criteria1:="x" y.Sheets("Table 3").Range("AC2").AutoFilter Field:=29, Criteria1:="x" y.Sheets("Table 4").Range("AD2").AutoFilter Field:=30, Criteria1:="x" y.Sheets("Table 8").Range("AE2").AutoFilter Field:=31, Criteria1:="x" y.Sheets("Table 11").Range("AF2").AutoFilter Field:=32, Criteria1:="x" y.Sheets("Table 12").Range("AG2").AutoFilter Field:=33, Criteria1:="x" y.Sheets("Table 13").Range("AH2").AutoFilter Field:=34, Criteria1:="x" y.Sheets("Table 1 S").Range("AA2").AutoFilter Field:=27, Criteria1:="x" y.Sheets("Table 2 S").Range("AB2").AutoFilter Field:=28, Criteria1:="x" y.Sheets("Table 3 S").Range("AC2").AutoFilter Field:=29, Criteria1:="x" y.Sheets("Table 4 S").Range("AD2").AutoFilter Field:=30, Criteria1:="x" y.Sheets("Table 8 S").Range("AE2").AutoFilter Field:=31, Criteria1:="x" y.Sheets("Table 11 S").Range("AF2").AutoFilter Field:=32, Criteria1:="x" y.Sheets("Table 12 S").Range("AG2").AutoFilter Field:=33, Criteria1:="x" y.Sheets("Table 13 S").Range("AH2").AutoFilter Field:=34, Criteria1:="x"
VBA AppActivate Issue
I have used VBA in Access extensively but have recently wandered out of my wheelhouse and into VBA/macros for Outlook, and am taking some knocks.
I am writing Outlook macros for an attorney who uses a Case Management application. That App uses a Case Number (unique identifier in a specific format) to organize saved documents into folders where the folder name is the Case Number. For the Outlook macros to function, they need that Case Number. First they search a message, and then the Clipboard, to see if there is a properly formatted Case Number.
If a Case Number is NOT found, the attorney needs the macro to Activate (bring to the foreground) his Case Management application, so that he can copy the number from there.
My problem is using the AppActivate procedure in VBA - it rarely works and is very inconsistent. Since I do not have his specific application on my pc, I am just trying to activate Notepad for testing purposes, and I cannot get this very simple step to work with any consistency. Most of the time, the macro just runs to completion without Activating the Called application. (Yes, Notepad IS running on my machine and NO it is not minimized).
Does anyone have insights in how to use AppActivate properly/differently? Or does anyone have suggestion how to bring a running application to foreground using a different approach?
Thanks for any insights. I appreciate it.
This code is excerpted from my VBA. It is not complete but does contain the AppActivate section:
'IF function returns "ERROR" or "NOT FOUND" take separate actions Select Case strFileNumberUse Case Is = "ERROR" MsgBox "The was an error trying to determine the File Number. Process cannot complete automatically." & vbCrLf & _ "You may need to attach a file manually" GoTo ExitHere Case Is = "NOT FOUND" 'Need to Ask about open CASE & POINT Application strOpenCasePointMsg = "You need to copy the File Number from Case & Point. Go there?" strOpenCasePointMsgStyle = vbYesNo + vbCritical + vbDefaultButton1 ' Define buttons. strTitle2 = "File Management" ' Define title. ' Display Dialog Box and Ask User if they want to go to Case & Point strOpenCasePointMsgResponse = MsgBox(strOpenCasePointMsg, strOpenCasePointMsgStyle, strTitle2) If strOpenCasePointMsgResponse = vbYes Then ' User chose Yes. AppActivate "Notepad" 'User will need to Go to Case & Point now, get File Number and Paste into Subject strFunctionResult = "OK" GoTo ExitHere Else ' User chose No. 'Get Default Folder Location for Opening File Attachment Browser strStartFolderPath = fnGetDefaultFilePath(strIsSecure) End If Case Else 'Continue with Process 'Get Default Folder Location for Opening File Attachment Browser strStartFolderPath = fnGetDefaultFilePath(strIsSecure) 'Append File Number to Default Path If Right(RTrim(strStartFolderPath), 1) <> "\" Then strStartFolderPath = strStartFolderPath & "\" & strFileNumberUse & "\" Else strStartFolderPath = strStartFolderPath & strFileNumberUse & "\" End If End Select
Double click button Internet Explorer Excel VBA
I am trying to simulate double clicking on an object in internet explorer.
Here is the HTML code:
<tr class="selected lead" style="height: 19px;"> <td class="sort-column" style="width: 1200px; line-height: 17px; text-indent: 19px;"> <img class="bi-tree-view-expand-icon" alt="" src="/resource/image/bid/triangle_collapsed_16.png"> <img class="icon" alt="" src=" /resource/image/bid/folder_closed_16.png"> My Content</td> <td class="horizontal-filler" style="width: 100px;"> </td></tr>
And the VBA code (which is currently working):
For Each Btn In IE.document.getElementsByClassName("sort-column") If Btn.innerText = "My Content" Then Btn.Click Exit For End If Next Btn
I can see the code pressing my button, but what I need is a double click of the button. Any help is greatly appreciated. Thanks!
Need to auto-populate another tab based on answers from original tab
I have a worksheet that has a series of questions in the 1st tab. I have a drop down list for each question that a user can select yes, no, corrected, etc. On the 2nd tab, I need the sheet to auto-populate the questions when a user selects Yes, Correct, etc. Pivot table is not an option as I need this to be automatic. I currently have macros built into the 2nd tab for this but if a user makes a change to the 1st tab, it does not auto refresh. Any ideas?
Error on second if when sending email
The code successfully sends the first email, but I am encountering an error on the second.
[Run-time error '-2147221238 (8004010a)': The item has been moved or deleted].
My goal is to use the button to automatically send reminder emails based on the criteria from the IF statements. The debug references the
.To = Recipientline.
Private Sub CommandButton1_Click() Dim objOutlook As Object Dim objEmail As Object Dim Row As Integer Dim Recipient As String Dim Requestor As String Dim CQID As String Dim lastRow As Long Set objOutlook = CreateObject("Outlook.Application") Set objEmail = objOutlook.CreateItem(olMailItem) lastRow = Cells(Rows.Count, 1).End(xlUp).Row With objEmail For Row = 2 To lastRow If Cells(Row, 12).Value = "10" And IsEmpty(Cells(Row, 13).Value) = True Then 'Prepares and sends email after 10 days and no reminder has already been sent. The second constraint is to prevent multiple emails from being sent if the button is pressed multiple times in the day. Recipient = Cells(Row, 14).Value Requestor = Cells(Row, 15).Value CQID = Cells(Row, 1).Value .To = Recipient '<---Debug reference .CC = Requestor .Subject = "Update Requested for " & CQID .Body = "Please send us an update on " & CQID '.Display .Send Cells(Row, 13).Value = "1st Reminder Sent" 'Prepares constraint for second reminder End If If Cells(Row, 12).Value = "15" And Cells(Row, 13).Value = "1st Reminder Sent" Then Recipient = Cells(Row, 14).Value Requestor = Cells(Row, 15).Value .To = Recipient .CC = Requestor .Subject = "Update Requested for " & CQID .Body = "Please send us an update on " & CQID '.Display .Send Cells(Row, 13).Value = "2nd Reminder Sent" End If Next Row End With Set objEmail = Nothing Set objOutlook = Nothing End Sub
Any help with the direct issue or suggestions for general improvements are greatly appreciated.
Using Excel Match function in Word VBA
I had a Word VBA macro that worked perfectly to populate data from an Excel workbook (which is acting as the 'database'). It called the excel Index and Match functions. However, as of today, I repeatedly get an error telling me that the Match and Index functions could not be found. What happened? Code is below.
On Error Resume Next Set xlapp = GetObject(, "Excel.Application") If Err Then bStart = True Set xlapp = CreateObject("Excel.Application") End If On Error GoTo 0 Set xldocbook = xlapp.Workbooks.Open("D:/Dr list.xlsx") Set xldocsheet = xldocbook.Worksheets(1) 'Set the current selection With xlapp.WorksheetFunction IUH = .Index(xldocsheet.Range("I:I"), .Match(DrName, xldocsheet.Range("E:E"), 0)) If IUH = "N" Then Address = .Index(xldocsheet.Range("K:K"), .Match(DrName, xldocsheet.Range("E:E"), 0)) CityStateZip = .Index(xldocsheet.Range("L:L"), .Match(DrName, xldocsheet.Range("E:E"), 0)) Else Address = "" CityStateZip = "" End If Phone = .Index(xldocsheet.Range("F:F"), .Match(DrName, xldocsheet.Range("E:E"), 0)) Fax = .Index(xldocsheet.Range("G:G"), .Match(DrName, xldocsheet.Range("E:E"), 0)) FullAddress = Address + " " + CityStateZip Debug.Print "Full Address is" + FullAddress End With xldocbook.Close If bStart = True Then xlapp.Quit End If
excel 2016 document still opened
Under Windows 10, I want to rename a folder which contains an excel 2016 document that was opened, modified and closed previously. However windows keeps telling me the document is still opened unless I completely close Excel and all the other documents (I don't want to close in fact). Does someone know how to wipe the incriminated document from Excel memory?
Excel 2016 crashes when using Debug.Print Tab() in Workbook_Open() event handler
Just something weird I noticed, tested on 2 computers with similar config (Windows 10 64-bit up-to-date with Excel 2016 32-bit), one was a clean install :
Simply create a new .xlsm workbook, and place the following in the ThisWorkbook.cls class module:
Private Sub Workbook_Open() Debug.Print Tab(10); "Hello!" End Sub
Save. Close. Open (if opens in PROTECTED VIEW mode, Enable editing, close and re-open). Excel crashes.
Minimal, Complete, Verifiable example.
Now the big question: why?
I tried to delay the problematic
Debug.Print Tab(10); "Hello!"by placing it in a Sub which I call a few sec after the Open event with
Application.OnTime Now() + (1/3600/24) * 5, "SubName", still crashing. I call it manually, it works just fine.
MSDN says nothing about it: Tab Function
Find last three cells in a row cut and paste to last three columns excel vba
My client has copied data from a .pdf file by highlighting it and pasting into a worksheet (the converter in Acrobat does not work well). This means the values in the last three cells of each row are staggered across the columns. This means the client has to select the cells from each row and cut and paste into new columns.
I looked at a way to do this but wracked my brain before I stumbled across Bruce Wayne's code which moves the last cell of each row to the end column:
Sub move_data() Dim LastRow As Long, lastCol As Long Dim ws As Worksheet Set ws = Worksheets("Sheet1") LastRow = ws.Range("A" & Rows.Count).End(xlUp).Row lastCol = ws.UsedRange.Columns.Count Cells.EntireColumn.AutoFit Dim i As Long For i = 1 To LastRow If ws.Cells(i, lastCol) = "" Then ws.Cells(i, ws.Cells(i, 1).End(xlToRight).Column).Cut ws.Cells(i,lastCol) End If Next i End Sub
So, that is exactly what I wanted but also the 2 cells to the left of last cell on that row. Could someone help me with the syntax on how to achieve this last bit.
NB. Running the macro makes Excel 2016 work very hard looping through the worksheet, which only has less than 100 rows. Any ideas on why the performance is slow?
Creating Custom Reminders in Outlook 2016, based off Free/Busy/Tentative status
I work for a Managed Services company and our internal ticketing system has a calendar that synchronizes with Outlook. Within the ticketing system, we can schedule techs to be "Tentatively" or "Firmly" assigned. In Outlook, it reflects tentative as tentative, and firm as busy.
What I'd like to do is setup Outlook to ONLY give pop-up reminders for meetings that reflect as busy (or alternatively to prevent it from giving reminders for tentative meetings). This would allow our techs to be only notified when they have a client established timing coming up, without creating noise when we don't want it. This also has to happen in the background as our dispatcher will set the tentative/firm status and it just appears on the tech's board. There is no way to customize the reminder settings within the ticketing system either.
There's no way that I've been able to find to do this via the UI, and so far I haven't found any scripts online. The closest thing I could find that might lead me in the right direction is (Auto accept/auto tentative accept) however I'm not sure what/how to modify that to accomplish my goal.
I'd like to clarify that I'm not a developer, and I realize that this will likely be some sort of VBA for Outlook, so ideally, either providing me with the script or providing me a page with a list of variables / commands / syntax that I could use to set this up would be greatly appreciated.
'Object reference not set to an instance of an object' error in Microsoft outlook 2016 addin
The outlook addin which was working completely fine in Outlook version 2010 is failing to work in 2016. I had used redemption.dll to get the control of the buttons in the ribbon and this was working correctly in 2010.In 2016 version i am getting the error as 'Object reference not set to an instance of an object' Is the redemption is the problem or any other issue. Please help me
Mail from shared mailbox remains stuck in outbox sometimes.
Mail from shared mailbox remains stuck in outbox sometimes. A solution that I have found (that partially works) is to add the delegatesentitemsstyle 1 registry file.
The problem we then face is that we have set up some mailboxes that would save the sent items in both the sent items folder of the user as well as in the shared mailbox. When we use the delegatesentitemsstyle solution this no longer works. Some of our users with this issue also noticed that in the shared mailboxes that the outbox is constantly shifting position.