5 Attachment(s)
Search for text in txt File using VBA, display rows where text found
Code in support of these Threads:
http://www.excelfox.com/forum/showth...0582#post10582
What code does in General:
This code will search for specific text in a text file
What code does in Specifically:
The code assumes that you have a simple text file looking something like this:
TextRowsInTextFile.jpg : https://imgur.com/upBY709
Attachment 2031
HotFixID
{EF8CD7FC-438D-49E3-A2C7-201052D9F2EF}
{8D2CDFAB-0079-43CC-A289-2F7A67F0A4DE}
{98D8F490-1F42-4F29-A59B-BF96D23A11BA}
{B730F010-3FCF-4E80-8A5A-C1DBEC0CF55A}
{B73E5AF4-40C6-4EA9-8F57-CFA70CC72BD6}
{BF11577A-6876-45AA-86C9-2BA4CFB8B019}
{E359D786-B101-4545-B8AB-8652323CF3CA}
{F4139440-5426-4C6F-909B-F71CEB1071B1}
{B2FAD7E1-67F9-435D-98BD-A77DBF4E1381}
Here is the example text file used in this explanation and currently hard coded into the code : “UpdatesOnVistaAspire4810TZG25thMarch.txt” : https://app.box.com/s/z90o8yj7iz0188yci34mu7gahe2tfhce
You can input , when prompted, a text string or text strings to look for. For more than one text string you should separate them by at least one space, like
__ B23 ___6872 35689
( The code below has those actual strings hard coded as the default search values )
Input Box Functioning.jpg : https://imgur.com/o9wlnhK https://imgur.com/JtnTDmy
Attachment 2030 Attachment 2034
The code will look for those text strings in all text file lines except the first.
( there is also a section to check the content of the first line, but it is 'commented out in the code below )
The code searches for those lines which contain any of those strings. In this demo example, one thing that I would be looking for is the rows in the text file containing B23 in them, so that would be the middle few in this screenshot .. B23 TextRowsInTextFile.JPG : https://imgur.com/JHRqJJc
Attachment 2032
The final result of the codes is to give you a string message which has a list of the text strings that you were looking for, and a list of the full text in any rows which contained that. The string is displayed in a message box. In addition if you are in the VB Editor Window and hit Ctrl+g , the you will see the results also in the immediate window. This latter has the advantage that you can copy the data to the clipboard by highlighting it and hitting Ctrl+c , ( or alternatively select the text and select the option to copy available via right mouse click ) : YouLookedForFindedWas.JPG: https://imgur.com/tyW4HSJ
Attachment 2033
Here is the code. It should be pasted into any File which is in the same Folder as the text file you want to search through. Currently the code is hard coded to search the file with name
“UpdatesOnVistaAspire4810TZG25thMarch.txt”
So you will need to change that to suit your text file name.
Code:
Sub CheqUpDates()
On Error GoTo GetLaid ' Instruction to replace / modify VBA default error handler by hanging on to the arousal this code starting from the labelled label code area
Rem 1) ActiviaExcretionLink, AEL. Checking Object link mechanismus
'1a) Exposing of interfaces for active RunableTimed data axctivated link
Dim ActiviEL As String ' "Pointer" to a "Blue Print" (or Form, Questionnaire not yet filled in, a template etc.)"Pigeon Hole" in Memory, sufficient in construction to house a piece of Paper with code text giving the relevant information for the particular Variable Type. VBA is sent to it when it passes it. In a Routine it may be given a particular “Value”, or (“Values” for Objects). There instructions say then how to do that and handle(store) that(those). At Dim the created Paper is like a Blue Print that has some empty spaces not yet filled in. A String is a bit tricky. The Blue Print code line Paper in the Pigeon Hole will allow to note the string Length and an Initial start memory Location. This Location well have to change frequently as strings of different length are assigned. Instructions will tell how to do this. Theoretically a special value vbNullString is set to aid in quick checks.. But..http://www.mrexcel.com/forum/excel-questions/361246-vbnullstring-2.html#post44116
Let ActiviEL = ThisWorkbook.Path & "\UpdatesOnVistaAspire4810TZG25thMarch.txt" 'Will be referrenced in code through an opened "route" to it
Dim LedgerFreiNummer As String: Let LedgerFreiNummer = "1" & "00" ' Not required in this code : https://www.excelforum.com/excel-general/1225401-value-of-true-1-or-1-vba-vs-worksheet.html
Dim AEL_Highway As Long: Let AEL_Highway = FreeFile("" & LedgerFreiNummer & "") ' Obtain from 2nd building phase (256-511) Ledger of available Highways, coercidentally to value 1_255 likely , bits of my 1 & 00
Rem 2) text file info
' '2a) Open File read first line check the sht - want Head
' Open ActiviEL For Input As AEL_Highway '
' Dim ShtHead As String
' Line Input #AEL_Highway, ShtHead ' Check substancialating for getting good Head
' If InStr(1, ShtHead, "HotFix", vbTextCompare) = 0 Then
' MsgBox prompt:="Got no HotFix IDin " & ShtHead
' Exit Sub
' Else
' Debug.Print ShtHead
' End If
' Close AEL_Highway ' Datei scheißen
'2b) "row" count in text file
Dim RecardRows As Long ' '_-' Long is very simple to handle, - final memory "size" type is known (123.456 and 000.001 have same "size" computer memory ) , and so a Address suggestion can be given for the next line when the variable is filled in. '( Long is a Big whole Number limit (-2,147,483,648 to 2,147,483,647) If you need some sort of validation the value should only be within the range of a Byte/Integer otherwise there's no point using anything but Long.--upon/after 32-bit, Integers (Short) need converted internally anyways, so a Long is actually faster. ) https://www.mrexcel.com/forum/excel-questions/803662-byte-backward-loop-4.html
Let RecardRows = 0
Dim strLine As String
Open ActiviEL For Input As AEL_Highway ' Activated embedded Link objectimocom Binary as to referencingmocomed aka AliAs AEL_Highway opened of now
Do Until EOF(AEL_Highway) 'Looping all lines in text file ' Solange bis Datei-Ende - EOF(AEL_Highway) will be set to true by the last a carriage liney mo not found a next line in Line Input #AEL_Highway, strLine
Line Input #AEL_Highway, strLine: Let RecardRows = RecardRows + 1 ' Inputed der liney mo a carriage returned after then record register count of it to that increase by the one done liney mo
Loop 'Do Until EOF(AEL_Highway) 'Looping all lines in text file
'Let RecardRows = RecardRows + 1 'would need to do this if I did not closeat '2a) and reopen in '2b)
Close AEL_Highway ' Datei scheißen - scheise drauf der Highway geschnmut - no longer activamoed AEL not activia mated mo
Rem 3) Prepare output Array for all text File data
Dim arrOut() As String: ReDim arrOut(1 To RecardRows) ' can declare to known size and type. We cannot use Dim arrOut(1 to RecardRows) as pre complie compile cannot do the RecardRows is not available: method ReDim is Runtime
Rem 4) Main loop for filling in Output Data =============================================
Open ActiviEL For Input As AEL_Highway
Dim RecardRow As Long ', strLine As String
For RecardRow = 1 To RecardRows '(Do Until EOF(AEL_Highway) 'Looping all lines in text file)
Line Input #AEL_Highway, strLine: Let arrOut(RecardRow) = strLine ' Zeile lesen - as before but this time place in element of output array
Next RecardRow ' ===== (Do Until EOF(AEL_Highway) 'Looping all lines in text file)===
Close AEL_Highway ' Datei schließen
Rem 5) search for specific strings
'5a) Bring in text or texts to be searched for, reduce multiple spaces to single spaces between if more than one given and, and split into array of those individual text strings https://powerspreadsheets.com/excel-vba-inputbox/ http://www.excelfox.com/forum/showthread.php/2227-VBA-Input-Pop-up-Boxes-Application-InputBox-Method-versus-VBA-InputBox-Function?p=10462#post10462
Dim strSrch As String '
Let strSrch = VBA.InputBox(prompt:="Type in all or part of text or texts to be searched for" & vbCrLf & "Seperate texts by at least one space", Title:="Input text to be searched for in text File lines", Default:="KB23 6872 35689", xpos:=100, ypos:=100)
Let strSrch = Evaluate("=TRIM(SUBSTITUTE(" & """" & strSrch & """" & ",CHAR(32)," & """" & " " & """" & "))") ' TRIM function trims the 7-bit ASCII space character (value 32). In the Unicode character set, there is an additional space character called the nonbreaking space character that has a decimal value of 160. This character is commonly used in Web pages as the HTML entity, . By itself, the TRIM function does not remove this nonbreaking space character. https://www.excelforum.com/excel-formulas-and-functions/1217202-is-there-a-function-similar-to-trim-but-that-only-removes-trailing-spaces-2.html
Dim SrchTxts() As String ' VBA strings function split to be used to get individual text into elements of an Array. The split function returns an array of string type elements
Let SrchTxts() = VBA.Split(strSrch, " ", -1, vbTextCompare) ' Split the ( strSrch , using space as delimiter , for unrestricted count , using text compare which is case insensitive )
For RecardRow = 2 To RecardRows 'At each record row
Dim Txtie As Long ' in default example this is 0 1 2
For Txtie = 0 To UBound(SrchTxts()) ' VBA Split retuns a 1 dimension array starting at indicie 0 For example we have indicies of 0 1 2 givig three elements in total of KB23 6872 35689
Dim strFnded As String
If InStr(1, arrOut(RecardRow), SrchTxts(Txtie), vbTextCompare) > 0 Then Let strFnded = strFnded & vbCrLf & arrOut(RecardRow) ' The returned postion along from the left ( starting from fist character , in the current row , looking for current text string , compare text which is case insensitive ) This will return 0 if not found and if found the postione along from the left in the row string where the search string part starts. So an found position will do for a find
Next Txtie
Next RecardRow
Rem 6) Display search results
Let strSrch = Replace(strSrch, " ", vbCrLf, 1, -1, vbBinaryCompare) 'replace in ( strSrch , space , with carriage return , start at and return from first character , no resriction on count , compare of exact computer memory so effectively case sensitive which is probably faster ) for convinent string list in output later
MsgBox prompt:="You looked for" & vbCrLf & strSrch & vbCrLf & vbCrLf & "Finded was" & strFnded
Debug.Print "You looked for" & vbCrLf & strSrch & vbCrLf & vbCrLf & "Finded was" & strFnded
Exit Sub ' Normal code ending
GetLaid: ' "Error handling code section http://www.excelfox.com/forum/showthread.php/2239-Resume-On-Error-GoTo-0-1-GoTo-Error-Handling-Statements-Runtime-VBA-Error-Handling-ORNeRe-GoRoT-N0Nula-1
MsgBox (Err.Description)
Close AEL_Highway ' Datei scheißen
End Sub
Some typical results in next post
5 Attachment(s)
Finding and deleteing .exd files
Appendix notes in support of these Threads:
http://www.excelfox.com/forum/showth...en-insert-them
http://www.excelfox.com/forum/showth...ommand-Buttons
Trying to find .exd files and delete them.
The results of most of what I have read or results of asking people suggests that they are usually findable if you look for a temp or temp somewhere in a file path
These appear a bit difficult to find sometimes . You can try:
_ manually navigating
_ a windows explorer search for *.exd
StarDotexeExplorerSearch.JPG : https://imgur.com/hfbC93Z
Attachment 2037
_ a search in a small bar using %temp% or %Temp%
You can get the small bar from either hitting WindowsKey+r or by selecting the Windows symbol
PerCenttempPerCentsearch.JPG : https://imgur.com/LypHLGY
Attachment 2038
PerCenttempPerCentsearch2.jpg : https://imgur.com/DZvycco
Attachment 2039
It seems a bit inconsistent which search finds what, but usually it is said that you find important places looking something like these:
C:\Users\username\AppData\Local\Temp\Excel8.0
C:\Users\username\AppData\Local\Temp\VBE
I found sometimes .exd files here also
C:\Users\username\Application Data\Microsoft\Forms
Some other typical places I found
C:\Dokumente und Einstellungen\Administrator\Application Data\Microsoft\Forms
C:\Dokumente und Einstellungen\Administrator\AppData\LocalLow
_.____._____________________
This is one of my typical attempts to get a ActiveX control button to_... either
_ insert into worksheet
or , if already three
_ work
_... by deleting .exd files
What I typically tried:
I looked here C:\Users\Elston\AppData\Local\Temp\Excel8.0
I found this:
C Users Elston AppData Local Temp Excel8.0.jpg https://imgur.com/doXstmr
Attachment 2040
I deleted that MSForms.exd File ( Excel had to be closed to do that ) : It had no effect. ( By the way, MSForms.exd gets made again every time I hit the button, or it appears to get made as soon as I open any file that either has or has ever had a control embedded in a worksheet)
In C:\Users\Elston\AppData\Local\Temp\VBE I found these:
C Users Elston AppData Local Temp VBE.jpg https://imgur.com/wjaZpXp
Attachment 2041
So…for these files I did:…
MSComctlLib.exd : I deleted this , - no effect
MSForms.exd : This could not be deleted with Excel 2003 open, it can when it is closed. But that had no effect
RefEdit.exd : This could not be deleted with Excel 2003 open, it can when it is closed. But no that had effect
( By the way, MSForms.exd and RefEdit.exd get made again every time I hit the button, or it appears to get made as soon as I open any file that either has, or has ever had, a control embedded in a worksheet )
In C:\Users\Elston\Application Data\Microsoft\Forms
I found these:
( https://imgur.com/Lv2kyhk )
So……for these files I did:…
RefEdit.exd This could not be deleted with Excel 2003 open, it can when it is closed. But that had no effect
mscomctllib.exd I deleted this: That had no effect
SHDocVw.exd I deleted this: That had no effect
( By the way, MSForms.exd gets made again every time I hit the button, or it appears to get made as soon as I open any file that either has or has ever had a control embedded in a worksheet)
_.._______________-
So for me none of that helped to get me a working ActiveX control Button in a worksheet.
:(
Master File After First Consolidation
Using Excel 2007 32 bit
S No |
Item |
Price |
Qty |
Total |
Distributed |
Task1 |
Task2 |
Task3 |
Task4 |
Completed |
Consolidated |
Comments |
Team Member |
Checked |
1 |
ABC01 |
$ 55.00 |
22 |
$ 1,210.00 |
02.Apr.2018 |
RT1 |
RT2 |
RT3 |
RT4 |
02.04.2018 |
02.Apr.2018 |
|
Raghu |
|
2 |
ABC02 |
$ 13.66 |
7 |
$ 95.62 |
02.Apr.2018 |
JT1 |
JT2 |
JT3 |
JT4 |
02.Apr.2018 |
02.Apr.2018 |
|
John |
|
3 |
ABC03 |
$ 12.99 |
5 |
$ 64.95 |
02.Apr.2018 |
GT1 |
GT2 |
GT3 |
GT4 |
02.Apr.2018 |
02.Apr.2018 |
|
Greg |
|
4 |
ABC04 |
$ 8.51 |
12 |
$ 102.12 |
02.Apr.2018 |
MT1 |
MT2 |
MT3 |
MT4 |
02.Apr.2018 |
02.Apr.2018 |
|
Margaret |
|
5 |
ABC05 |
$ 7.22 |
62 |
$ 447.64 |
02.Apr.2018 |
RT1 |
RT2 |
RT3 |
RT4 |
02.04.2018 |
02.Apr.2018 |
|
Raghu |
|
6 |
ABC06 |
$ 3.99 |
35 |
$ 139.65 |
02.Apr.2018 |
JT1 |
JT2 |
JT3 |
JT4 |
02.Apr.2018 |
02.Apr.2018 |
|
John |
|
7 |
ABC07 |
$ 333.45 |
99 |
$ 33,011.55 |
02.Apr.2018 |
GT1 |
GT2 |
GT3 |
GT4 |
02.Apr.2018 |
02.Apr.2018 |
|
Greg |
|
8 |
ABC08 |
$ 11.99 |
1 |
$ 11.99 |
02.Apr.2018 |
MT1 |
MT2 |
MT3 |
MT4 |
02.Apr.2018 |
02.Apr.2018 |
|
Margaret |
|
9 |
ABC09 |
$ 741.99 |
101 |
$ 74,940.99 |
02.Apr.2018 |
RT1 |
RT2 |
RT3 |
RT4 |
02.04.2018 |
02.Apr.2018 |
|
Raghu |
|
10 |
ABC10 |
$ 55.00 |
22 |
$ 1,210.00 |
02.Apr.2018 |
JT1 |
JT2 |
JT3 |
JT4 |
02.Apr.2018 |
02.Apr.2018 |
|
John |
|
11 |
ABC11 |
$ 13.66 |
7 |
$ 95.62 |
02.Apr.2018 |
GT1 |
GT2 |
GT3 |
GT4 |
02.Apr.2018 |
02.Apr.2018 |
|
Greg |
|
12 |
ABC12 |
$ 12.99 |
5 |
$ 64.95 |
02.Apr.2018 |
MT1 |
MT2 |
MT3 |
MT4 |
02.Apr.2018 |
02.Apr.2018 |
|
Margaret |
|
13 |
ABC13 |
$ 8.51 |
12 |
$ 102.12 |
02.Apr.2018 |
RT1 |
RT2 |
RT3 |
RT4 |
02.04.2018 |
02.Apr.2018 |
|
Raghu |
|
14 |
ABC14 |
$ 7.22 |
62 |
$ 447.64 |
02.Apr.2018 |
JT1 |
JT2 |
JT3 |
JT4 |
02.Apr.2018 |
02.Apr.2018 |
|
John |
|
15 |
ABC15 |
$ 3.99 |
35 |
$ 139.65 |
02.Apr.2018 |
GT1 |
GT2 |
GT3 |
GT4 |
02.Apr.2018 |
02.Apr.2018 |
|
Greg |
|
16 |
ABC16 |
$ 333.45 |
99 |
$ 33,011.55 |
02.Apr.2018 |
MT1 |
MT2 |
MT3 |
MT4 |
02.Apr.2018 |
02.Apr.2018 |
|
Margaret |
|
17 |
ABC17 |
$ 11.99 |
1 |
$ 11.99 |
02.Apr.2018 |
RT1 |
RT2 |
RT3 |
RT4 |
02.04.2018 |
02.Apr.2018 |
|
Raghu |
|
18 |
ABC18 |
$ 741.99 |
101 |
$ 74,940.99 |
02.Apr.2018 |
JT1 |
JT2 |
JT3 |
JT4 |
02.Apr.2018 |
02.Apr.2018 |
|
John |
|
19 |
ABC19 |
$ 55.00 |
22 |
$ 1,210.00 |
02.Apr.2018 |
GT1 |
GT2 |
GT3 |
GT4 |
02.Apr.2018 |
02.Apr.2018 |
|
Greg |
|
20 |
ABC20 |
$ 13.66 |
7 |
$ 95.62 |
02.Apr.2018 |
MT1 |
MT2 |
MT3 |
MT4 |
02.Apr.2018 |
02.Apr.2018 |
|
Margaret |
|
21 |
ABC21 |
$ 12.99 |
5 |
$ 64.95 |
02.Apr.2018 |
|
|
|
|
|
|
|
Raghu |
|
22 |
ABC22 |
$ 8.51 |
12 |
$ 102.12 |
02.Apr.2018 |
JT1 |
JT2 |
JT3 |
JT4 |
02.Apr.2018 |
02.Apr.2018 |
|
John |
|
23 |
ABC23 |
$ 7.22 |
62 |
$ 447.64 |
02.Apr.2018 |
|
|
|
|
|
|
|
Greg |
|
24 |
ABC24 |
$ 3.99 |
35 |
$ 139.65 |
02.Apr.2018 |
MT1 |
MT2 |
MT3 |
MT4 |
02.Apr.2018 |
02.Apr.2018 |
|
Margaret |
|
25 |
ABC25 |
$ 333.45 |
99 |
$ 33,011.55 |
02.Apr.2018 |
|
|
|
|
|
|
|
Raghu |
|
26 |
ABC26 |
$ 11.99 |
1 |
$ 11.99 |
02.Apr.2018 |
|
|
|
|
|
|
|
John |
|
27 |
ABC27 |
$ 741.99 |
101 |
$ 74,940.99 |
02.Apr.2018 |
|
|
|
|
|
|
|
Greg |
|
28 |
ABC28 |
$ 55.00 |
22 |
$ 1,210.00 |
02.Apr.2018 |
MT1 |
MT2 |
MT3 |
MT4 |
02.Apr.2018 |
02.Apr.2018 |
|
Margaret |
|
29 |
ABC29 |
$ 13.66 |
7 |
$ 95.62 |
02.Apr.2018 |
|
|
|
|
|
|
|
Raghu |
|
30 |
ABC30 |
$ 12.99 |
5 |
$ 64.95 |
02.Apr.2018 |
|
|
|
|
|
|
|
John |
|
31 |
ABC31 |
$ 8.51 |
12 |
$ 102.12 |
02.Apr.2018 |
|
|
|
|
|
|
|
Greg |
|
32 |
ABC32 |
$ 7.22 |
62 |
$ 447.64 |
02.Apr.2018 |
|
|
|
|
|
|
|
Margaret |
|
33 |
ABC33 |
$ 3.99 |
35 |
$ 139.65 |
02.Apr.2018 |
|
|
|
|
|
|
|
Raghu |
|
34 |
ABC34 |
$ 333.45 |
99 |
$ 33,011.55 |
02.Apr.2018 |
|
|
|
|
|
|
|
John |
|
35 |
ABC35 |
$ 11.99 |
1 |
$ 11.99 |
02.Apr.2018 |
|
|
|
|
|
|
|
Greg |
|
36 |
ABC36 |
$ 741.99 |
101 |
$ 74,940.99 |
02.Apr.2018 |
|
|
|
|
|
|
|
Margaret |
|
37 |
ABC37 |
$ 55.00 |
22 |
$ 1,210.00 |
02.Apr.2018 |
|
|
|
|
|
|
|
Raghu |
|
38 |
ABC38 |
$ 13.66 |
7 |
$ 95.62 |
02.Apr.2018 |
|
|
|
|
|
|
|
John |
|
39 |
ABC39 |
$ 12.99 |
5 |
$ 64.95 |
02.Apr.2018 |
|
|
|
|
|
|
|
Greg |
|
40 |
ABC40 |
$ 8.51 |
12 |
$ 102.12 |
02.Apr.2018 |
|
|
|
|
|
|
|
Margaret |
|
41 |
ABC41 |
$ 7.22 |
62 |
$ 447.64 |
02.Apr.2018 |
|
|
|
|
|
|
|
Raghu |
|
42 |
ABC42 |
$ 3.99 |
35 |
$ 139.65 |
02.Apr.2018 |
|
|
|
|
|
|
|
John |
|
43 |
ABC43 |
$ 333.45 |
99 |
$ 33,011.55 |
02.Apr.2018 |
|
|
|
|
|
|
|
Greg |
|
44 |
ABC44 |
$ 11.99 |
1 |
$ 11.99 |
02.Apr.2018 |
|
|
|
|
|
|
|
Margaret |
|
45 |
ABC45 |
$ 741.99 |
101 |
$ 74,940.99 |
02.Apr.2018 |
|
|
|
|
|
|
|
Raghu |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Worksheet: OriginalData
Typical Updated Data File, "Raghu.xlsx" after first consolidation
Typical Updated Data File, "Raghu.xlsx" after first consolidation,
with the column L updated appropriately with the consolidation date
Using Excel 2007 32 bit
| Row\Col |
A |
B |
C |
D |
E |
F |
G |
H |
I |
J |
K |
L |
M |
N |
O |
1 |
S No |
Item |
Price |
Qty |
Total |
Distributed |
Task1 |
Task2 |
Task3 |
Task4 |
Completed |
Consolidated |
Comments |
Team Member |
|
2 |
1 |
ABC01 |
$ 55.00 |
22 |
$ 1,210.00 |
02.Apr.2018 |
RT1 |
RT2 |
RT3 |
RT4 |
02. Apr 18 |
02.Apr.2018 |
|
Raghu |
|
3 |
5 |
ABC05 |
$ 7.22 |
62 |
$ 447.64 |
02.Apr.2018 |
RT1 |
RT2 |
RT3 |
RT4 |
02. Apr 18 |
02.Apr.2018 |
|
Raghu |
|
4 |
9 |
ABC09 |
$ 741.99 |
101 |
$ 74,940.99 |
02.Apr.2018 |
RT1 |
RT2 |
RT3 |
RT4 |
02. Apr 18 |
02.Apr.2018 |
|
Raghu |
|
5 |
13 |
ABC13 |
$ 8.51 |
12 |
$ 102.12 |
02.Apr.2018 |
RT1 |
RT2 |
RT3 |
RT4 |
02. Apr 18 |
02.Apr.2018 |
|
Raghu |
|
6 |
17 |
ABC17 |
$ 11.99 |
1 |
$ 11.99 |
02.Apr.2018 |
RT1 |
RT2 |
RT3 |
RT4 |
02. Apr 18 |
02.Apr.2018 |
|
Raghu |
|
7 |
21 |
ABC21 |
$ 12.99 |
5 |
$ 64.95 |
02.Apr.2018 |
|
|
|
|
|
|
|
Raghu |
|
8 |
25 |
ABC25 |
$ 333.45 |
99 |
$ 33,011.55 |
02.Apr.2018 |
|
|
|
|
|
|
|
Raghu |
|
9 |
29 |
ABC29 |
$ 13.66 |
7 |
$ 95.62 |
02.Apr.2018 |
|
|
|
|
|
|
|
Raghu |
|
10 |
33 |
ABC33 |
$ 3.99 |
35 |
$ 139.65 |
02.Apr.2018 |
|
|
|
|
|
|
|
Raghu |
|
11 |
37 |
ABC37 |
$ 55.00 |
22 |
$ 1,210.00 |
02.Apr.2018 |
|
|
|
|
|
|
|
Raghu |
|
12 |
41 |
ABC41 |
$ 7.22 |
62 |
$ 447.64 |
02.Apr.2018 |
|
|
|
|
|
|
|
Raghu |
|
13 |
45 |
ABC45 |
$ 741.99 |
101 |
$ 74,940.99 |
02.Apr.2018 |
|
|
|
|
|
|
|
Raghu |
|
14 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Worksheet: Tabelle1
Checked Library Infomation Excel 2003 Excel 2007 Excel 2010
Some sample data for other Posts and Threads:
http://www.eileenslounge.com/viewtopic.php?f=30&t=29652
Using this code:
Code:
Sub Its() ' snb 2017
Dim It As Variant
For Each It In ThisWorkbook.VBProject.References
Dim strIts As String
Let strIts = strIts & "Description:" & vbTab & It.Description & vbCr & "Name:" & vbTab & vbTab & It.Name & vbCr & "Buitin:" & vbTab & vbTab & It.BuiltIn & vbCr & "Minor:" & vbTab & vbTab & It.minor & vbCr & "Major:" & vbTab & vbTab & It.major & vbCr & "FullPath:" & vbTab & vbTab & It.fullpath & vbCr & "GUID:" & vbTab & vbTab & It.GUID & vbCr & "Type:" & vbTab & vbTab & It.Type & vbCr & "Isbroken:" & vbTab & vbTab & It.isbroken & vbCr & vbCr
Next It
Debug.Print strIts ' From VB Editor Ctrl+g to get Immediate Window from which info can be copied
End Sub
Here some results. ( If anyone passing has other Excel versions and would like to pass on what the code above gives, then that would be nice, thanks :) )
Excel 2007
Code:
Description: Visual Basic For Applications
Name: VBA
Buitin: Wahr
Minor: 0
Major: 4
FullPath: C:\Program Files\Common Files\Microsoft Shared\VBA\VBA6\VBE6.DLL
GUID: {000204EF-0000-0000-C000-000000000046}
Type: 0
Isbroken: Falsch
Description: Microsoft Excel 12.0 Object Library
Name: Excel
Buitin: Wahr
Minor: 6
Major: 1
FullPath: C:\Program Files\Microsoft Office\Office12\EXCEL.EXE
GUID: {00020813-0000-0000-C000-000000000046}
Type: 0
Isbroken: Falsch
Description: OLE Automation
Name: stdole
Buitin: Falsch
Minor: 0
Major: 2
FullPath: C:\Windows\system32\stdole2.tlb
GUID: {00020430-0000-0000-C000-000000000046}
Type: 0
Isbroken: Falsch
Description: Microsoft Office 12.0 Object Library
Name: Office
Buitin: Falsch
Minor: 4
Major: 2
FullPath: C:\Program Files\Common Files\Microsoft Shared\OFFICE12\MSO.DLL
GUID: {2DF8D04C-5BFA-101B-BDE5-00AA0044DE52}
Type: 0
Isbroken: Falsch
Description: Microsoft Word 12.0 Object Library
Name: Word
Buitin: Falsch
Minor: 4
Major: 8
FullPath: C:\Program Files\Microsoft Office\Office12\MSWORD.OLB
GUID: {00020905-0000-0000-C000-000000000046}
Type: 0
Isbroken: Falsch
Excel 2003
Code:
Description: Visual Basic For Applications
Name: VBA
Buitin: Wahr
Minor: 0
Major: 4
FullPath: C:\Program Files\Common Files\Microsoft Shared\VBA\VBA6\VBE6.DLL
GUID: {000204EF-0000-0000-C000-000000000046}
Type: 0
Isbroken: Falsch
Description: Microsoft Excel 11.0 Object Library
Name: Excel
Buitin: Wahr
Minor: 5
Major: 1
FullPath: C:\Program Files\Microsoft Office\OFFICE11\EXCEL.EXE
GUID: {00020813-0000-0000-C000-000000000046}
Type: 0
Isbroken: Falsch
Description: OLE Automation
Name: stdole
Buitin: Falsch
Minor: 0
Major: 2
FullPath: C:\Windows\system32\stdole2.tlb
GUID: {00020430-0000-0000-C000-000000000046}
Type: 0
Isbroken: Falsch
Description: Microsoft Office 11.0 Object Library
Name: Office
Buitin: Falsch
Minor: 3
Major: 2
FullPath: C:\Program Files\Common Files\Microsoft Shared\OFFICE11\MSO.DLL
GUID: {2DF8D04C-5BFA-101B-BDE5-00AA0044DE52}
Type: 0
Isbroken: Falsch
Description: Microsoft Word 12.0 Object Library
Name: Word
Buitin: Falsch
Minor: 4
Major: 8
FullPath: C:\Program Files\Microsoft Office\Office12\MSWORD.OLB
GUID: {00020905-0000-0000-C000-000000000046}
Type: 0
Isbroken: Falsch
Excel 2010
Code:
Description: Visual Basic For Applications
Name: VBA
Buitin: Wahr
Minor: 1
Major: 4
FullPath: C:\PROGRA~2\COMMON~1\MICROS~1\VBA\VBA7\VBE7.DLL
GUID: {000204EF-0000-0000-C000-000000000046}
Type: 0
Isbroken: Falsch
Description: Microsoft Excel 14.0 Object Library
Name: Excel
Buitin: Wahr
Minor: 7
Major: 1
FullPath: C:\Program Files (x86)\Microsoft Office\Office14\EXCEL.EXE
GUID: {00020813-0000-0000-C000-000000000046}
Type: 0
Isbroken: Falsch
Description: OLE Automation
Name: stdole
Buitin: Falsch
Minor: 0
Major: 2
FullPath: C:\Windows\SysWOW64\stdole2.tlb
GUID: {00020430-0000-0000-C000-000000000046}
Type: 0
Isbroken: Falsch
Description: Microsoft Office 14.0 Object Library
Name: Office
Buitin: Falsch
Minor: 5
Major: 2
FullPath: C:\Program Files (x86)\Common Files\Microsoft Shared\OFFICE14\MSO.DLL
GUID: {2DF8D04C-5BFA-101B-BDE5-00AA0044DE52}
Type: 0
Isbroken: Falsch
Description: Microsoft Word 14.0 Object Library
Name: Word
Buitin: Falsch
Minor: 5
Major: 8
FullPath: C:\Program Files (x86)\Microsoft Office\Office14\MSWORD.OLB
GUID: {00020905-0000-0000-C000-000000000046}
Type: 0
Isbroken: Falsch
Test data supplied by Thainguyen
To support solution to this Thread:
http://www.excelfox.com/forum/showth...and-send-email
Test data supplied by Thainguyen for this Thread :
http://www.excelfox.com/forum/showth...and-send-email
Code:
Using Excel 2007 32 bit
| Row\Col |
A |
B |
C |
D |
E |
F |
G |
H |
I |
J |
K |
N |
|---|
1 |
Equipment PM |
|
|
|
|
|
|
|
|
|
|
|
2 |
Machine EQ.ID |
Manufacture |
Model |
Description |
Serial Number |
Weekly
Date of Service |
Weekly
Next Service |
Monthly
Date of Service |
Monthly
Next Service |
Quarterly
Date of Service |
Quarterly
Next Service |
Softwear |
3 |
|
|
|
|
|
|
|
|
|
|
|
|
4 |
1 |
JUKI |
GKG GL |
GL SCREEN PRINTER |
A123 |
06.04.2018 |
13.04.2018 |
15.03.2018 |
12.04.2018 |
N/A |
N/A |
|
5 |
2 |
JUKI |
KE-1070L |
SMT Placement Machine |
A124 |
11.04.2018 |
18.04.2018 |
28.03.2018 |
25.04.2018 |
N/A |
N/A |
|
6 |
9 |
ACE Production |
KISS-101B |
Selective Wave Solder |
A125 |
06.04.2018 |
13.04.2018 |
15.03.2018 |
12.04.2018 |
N/A |
N/A |
|
7 |
59 |
Heller |
1826 MK5 |
Reflow Oven |
A126 |
N/A |
N/A |
16.03.2018 |
13.04.2018 |
N/A |
N/A |
|
8 |
62 |
Exit Sign -- N/A -- Exit Lights |
N/A |
N/A |
A127 |
N/A |
N/A |
N/A |
N/A |
N/A |
N/A |
|
9 |
69 |
South-Tek System |
N2-Gen 35ST |
Nitrogen Generator |
A128 |
10.04.2018 |
17.04.2018 |
N/A |
N/A |
09.03.2018 |
06.04.2018 |
|
10 |
75 |
ACE Production |
KISS-102 |
Selective Wave Solder |
A129 |
16.04.2018 |
23.04.2018 |
N/A |
N/A |
N/A |
N/A |
|
11 |
101 |
FKN system |
N100 Nibbler |
Dispensing |
A130 |
N/A |
N/A |
N/A |
N/A |
04.04.2018 |
02.05.2018 |
|
12 |
109 |
Mycronic |
MY200sx |
SMT Machine |
A131 |
N/A |
N/A |
N/A |
N/A |
N/A |
N/A |
|
13 |
112 |
X-TEK |
XTV-160 |
X-Ray System |
A132 |
N/A |
N/A |
N/A |
N/A |
N/A |
N/A |
|
14 |
113 |
MIRTEC |
MV-6 OMNI |
AOI |
A133 |
N/A |
N/A |
N/A |
N/A |
N/A |
N/A |
|
15 |
116 |
JUKI |
KE-2060RL |
SMT Placement Machine |
A134 |
N/A |
N/A |
N/A |
N/A |
N/A |
N/A |
|
16 |
127 |
ELGI |
EG22-150 |
Air Compressor |
A135 |
N/A |
N/A |
N/A |
N/A |
N/A |
N/A |
|
17 |
128 |
Juki |
KE-2050 |
SMT |
A136 |
N/A |
N/A |
N/A |
N/A |
N/A |
N/A |
|
18 |
137 |
Juki |
K3 |
Screen printer |
A137 |
06.04.2018 |
13.04.2018 |
N/A |
N/A |
N/A |
N/A |
|
19 |
141 |
Heller |
1826 MK5 |
Reflow Oven |
A138 |
N/A |
N/A |
N/A |
N/A |
N/A |
N/A |
|
20 |
142 |
NISSAN |
MCU-112A331.V |
Forklift |
A139 |
N/A |
N/A |
N/A |
N/A |
15.02.2018 |
15.03.2018 |
|
21 |
142 |
NISSAN/yearly oil change and lube |
MCU-112A331.V |
Forklift |
A140 |
N/A |
N/A |
N/A |
N/A |
N/A |
N/A |
|
22 |
|
|
|
|
|
|
|
|
|
|
28.01.1900 |
|
23 |
|
|
|
|
|
|
|
|
|
|
|
|
Worksheet: Equipment PM
1 Attachment(s)
Re Post code with Code tags
To support this Thread
http://www.excelfox.com/forum/showth...0679#post10679
Re post code in Code tags, Like ....
Please use CODE TAGS if you are writing codes in your post.
To use code tags,
either
select your entire code and press the code tag button # in the editor below,
or
simply type your code as below
[Code]Your Code Here[/Code]
[Code]
Your Code Here
[/Code]
[Code]
Private Sub cmdNot_Click()
Dim OutApp As Object
Dim OutMail As Object
…………………….
……………..
End Sub
[/Code]
BBCodeCodeTags.JPG : https://imgur.com/4HunNcs
Attachment 2060
_.__________________
If you post using Code tags, then it will come out in the final post in a Code Window, like this:
Code:
Private Sub cmdNot_Click()
Dim OutApp As Object
Dim OutMail As Object
Dim fileName As String
Dim mSubject As String
Dim signature As String
Dim fname As String
Dim mBody As String
Dim rng As Range
Dim rng1 As Range
Dim ws As Worksheet
Dim mailTo As String
Set ws = Sheets("MRO")
fname = ws.Range("B4")
mSubject = "MRO " & " For " & Range("C6").Value
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
'mBody = "2-SO\Material Request Form .xlsm"
Dim Path As String
mBody = "<font size=""3"" face=""Calibri"">" & _
"Dear Team,<br><br>" & _
"Please open the file from below link and put your signature on the respective cell after you completed your task.<br><B>" & _
fileName & ".xlsm" & "</B> is created.<br>" & _
"Click on this link to open the file : " & _
"<A HREF=""file://" & Path & fileName & ".xlsm" & _
""">Files are saved here</A>" & "-->" & Range("C6").Value & _
"<br><br>Best Regards," & _
"<br><br></font>"
With OutMail
.display
End With
signature = OutMail.body
With Application
.EnableEvents = False
.ScreenUpdating = False
End With
With OutMail
'.To = "email"
.To = ""
.CC = ""
.BCC = ""
.Subject = mSubject
'.body = "Dear Team," & vbCrLf & vbCrLf & "Please open the file from below link and put your signature on the respective cell and save the sheet"
'.htmlbody = RangetoHTML(rng)
.htmlbody = mBody
'.Attachments.Add fileName
.display
End With
'ws.PageSetup.RightHeader = "&""Calibri,italic""&11& " & ws.Range("A1")
ActiveWorkbook.Close False
ActiveWorkbook.Close
On Error GoTo 0
Set OutMail = Nothing
Set OutApp = Nothing
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
Share account for testing file access from a hyperlink in a received EMail
Share account for testing file access from a hyperlink in a received EMail
In support of a possible solution to this post in this Thread:
http://www.excelfox.com/forum/showth...0724#post10724
It is required to have a simple hyperlink to an Excel File appear in the received Email sent to members of a team.
I am not sure currently how to get a link directly to the File.
An second alternative involves storing the file at a File sharing site and using the link to the file as the URL part of a hyperlink.
This post discusses the setting up of such an account to allow storing of, and sharing via a supplied link to, the file.
As an example of a file sharing site we consider the free version of box.net
Some googling my be needed to finally get at the free version which may go under the name of “free” , “Individual rate”, “Personal free”
Currently you need to find your way to the free 10GB offer. This is currently at this link:
https://account.box.com/signup/n/personal#fbms6
Free10GB box net account register.JPG : https://imgur.com/NB3GThi
Note , by registering, you can choose a language to suit you.
Free10GB Select language .JPG : : https://imgur.com/aNzW1kq
( You can change the language to a different one after registering also
Free10GB Change language .JPG : https://imgur.com/IosqbAI )
For this registering , I use the created gmail account used for experiments in the current thread which this post supports, excellearning12@gmail.com ( excelfox Thread : http://www.excelfox.com/forum/showth...and-send-email )
The password I pass on privately to those needing
Free10GB box net account register 2.JPG : https://imgur.com/Y2pLogO
Free10GB box net account register 3.JPG : https://imgur.com/QhCR8fP
Free10GB box net account register Verify Email 4.JPG : https://imgur.com/ffG7erw
Various steps are then gone through, they may be slightly different to the following:
At some point you should you should see the possibility to upload a file, following steps similar to these:
Free10GB box net 5 .JPG : https://imgur.com/lNWvQwF
To upload a file and get a URL link to use in a hyperlink to it:
Upload Files:
Free10GB box net 6 .JPG : https://imgur.com/rTU1Xbk
Select a file:
Free10GB box net 7 .JPG : https://imgur.com/wKKlqoO
Select share to obtain a URL link to the file :
Free10GB box net 8 .JPG : https://imgur.com/R3VbyhR
Copy link to be used in Hyperlink :
Free10GB box net 9 .JPG : https://imgur.com/8yaYwaK
Testing Hyperlinks in received EMail
Testing codes in support of this Thread
http://www.excelfox.com/forum/showth...0727#post10727
Codes for Alf and sandy666
Code:
Option Explicit
Sub SendfromExcelVBAExpgmail()
Rem 6 EMail send 'For info see: http://www.excelfox.com/forum/showthread.php/2233-Urgent-support-needed-Multiple-emails-multiple-Excel-workbooks-at-once#post10519
'Working at my end With my With End With Created LCDCW Library, (LCD 1.0 Library ) (Linking Configuration Data_Cods Wollups)
'6a(i)
With CreateObject("CDO.Message") ' -Linking Cods Wollups--------
Dim LCD_CW As String: Let LCD_CW = "http://schemas.microsoft.com/cdo/configuration/"
.Configuration(LCD_CW & "smtpusessl") = True '
.Configuration(LCD_CW & "smtpauthenticate") = 1 '
' ' Sever info
.Configuration(LCD_CW & "smtpserver") = "smtp.gmail.com" ' "smtp.office365.com" ' "smtp.live.com" ' "smtp-mail.outlook.com" ' "smtp.live.com" ' "smtp.gmail.com" ' "securesmtp.t-online.de" "smtp.mail.yahoo.com" "smtp.live.com" "pod51017.outlook.com" "smtp-mail.outlook.com" "smtp.live.com" "securesmtp.t-online.de"
' The mechanism to use to send messages.
.Configuration(LCD_CW & "sendusing") = 2 ' Based on the LCD_OLE Data Base of type DBTYPE_I4
.Configuration(LCD_CW & "smtpserverport") = 465 ' 465 or 25 for gmail '587 ' 25 ' 465 or 25 for t-online.de 'or 587 'or 25
'
.Configuration(LCD_CW & "sendusername") = "ExcelVBAExp@gmail.com" '
.Configuration(LCD_CW & "sendpassword") = "xxxxxxxxxxxxxx" ' '
' .Configuration(LCD_CW & "sendusername") = "YourEMailAddress"
' .Configuration(LCD_CW & "sendpassword") = "YourEMailPassword"
' Optional - How long to try
.Configuration(LCD_CW & "smtpconnectiontimeout") = 30 '
' Intraction protocol is Set/ Updated
.Configuration.Fields.Update '
'End With 6a(i)' ---------------------- my Created LCDCW Library
'6a(ii) With ' -- ' Data to be sent--- my Created LCDCW Library
Dim strHTML As String: Let strHTML = "<font size=""3"" face=""Calibri"">" & _
"This is sent from EMail account:" & _
"<br>Username: ""ExcelVBAExp@gmail.com""" & _
"<br>Password: ""xxxxxxxxxxxxxxxxxxxxxx""" & _
"<br><br>" & _
"<br>Please click on the 5 links below and tell me what happens, thanks!" & _
"<br>1 <A HREF=""https://app.box.com/s/x01liz3ralbdrt152i52fpwb0wx40ff3"">link to box net cloud free file sharing</A>" & _
"<br>2 <A HREF=""file://" & ThisWorkbook.Path & "\" & "Test file DOThtm to be stored on your computer to try to open with a Hyperlink in a received EMail.htm"""">htm file on your computer</A>" & _
"<br>3 <A HREF=""file://" & ThisWorkbook.Path & "\" & "Test file DOThtm to be stored on your computer to try to open with a Hyperlink in a received EMail.htm"""">htm file on your computer</A>" & _
"<br>4 <A HREF=""file://" & ThisWorkbook.Path & "\" & "Empty test file DOTxls stored on your computer to try to open from Hyperlink in arrived Email.xls"""">empty xls file on your computer</A>" & _
"<br>5 <A HREF=""file:///" & ThisWorkbook.Path & "\" & "Empty test file DOTxls stored on your computer to try to open from Hyperlink in arrived Email.xls"""">empty xls file on your computer</A>"
.To "xxxxxxxxxxxxxx"
.CC "xxxxxxxxxxxxxa"
.BCC = ""
.from = """ExcelVBAExp@gmail.com"" <ExcelVBAExp@gmail.com>"
.Subject = "Sent from EMail address: ExcelVBAExp@gmail.com"
.htmlbody = strHTML
.Send ' Do it
End With ' 6a(ii) CreateObject("CDO.Message") ---my Created LCDCW Library
End Sub
Sub SendfromFahrradprinzessinunterwegsgmail()
Rem 6 EMail send 'For info see: http://www.excelfox.com/forum/showthread.php/2233-Urgent-support-needed-Multiple-emails-multiple-Excel-workbooks-at-once#post10519
'Working at my end With my With End With Created LCDCW Library, (LCD 1.0 Library ) (Linking Configuration Data_Cods Wollups)
'6a(i)
With CreateObject("CDO.Message") ' -Linking Cods Wollups--------
Dim LCD_CW As String: Let LCD_CW = "http://schemas.microsoft.com/cdo/configuration/"
.Configuration(LCD_CW & "smtpusessl") = True '
.Configuration(LCD_CW & "smtpauthenticate") = 1 '
' ' Sever info
.Configuration(LCD_CW & "smtpserver") = "smtp.gmail.com" ' "smtp.office365.com" ' "smtp.live.com" ' "smtp-mail.outlook.com" ' "smtp.live.com" ' "smtp.gmail.com" ' "securesmtp.t-online.de" "smtp.mail.yahoo.com" "smtp.live.com" "pod51017.outlook.com" "smtp-mail.outlook.com" "smtp.live.com" "securesmtp.t-online.de"
' The mechanism to use to send messages.
.Configuration(LCD_CW & "sendusing") = 2 ' Based on the LCD_OLE Data Base of type DBTYPE_I4
.Configuration(LCD_CW & "smtpserverport") = 465 ' 465 or 25 for gmail '587 ' 25 ' 465 or 25 for t-online.de 'or 587 'or 25
'
.Configuration(LCD_CW & "sendusername") = "Fahrradprinzessinunterwegs@gmail.com" '
.Configuration(LCD_CW & "sendpassword") = "xxxxxxxxxxxxx" ' '
' .Configuration(LCD_CW & "sendusername") = "YourEMailAddress"
' .Configuration(LCD_CW & "sendpassword") = "YourEMailPassword"
' Optional - How long to try
.Configuration(LCD_CW & "smtpconnectiontimeout") = 30 '
' Intraction protocol is Set/ Updated
.Configuration.Fields.Update '
'End With 6a(i)' ---------------------- my Created LCDCW Library
'6a(ii) With ' -- ' Data to be sent--- my Created LCDCW Library
Dim strHTML As String: Let strHTML = "<font size=""3"" face=""Calibri"">" & _
"This is sent from EMail account:" & _
"<br>Username: ""Fahrradprinzessinunterwegs@gmail.com""" & _
"<br>Password: ""xxxxxxxxxxxxxxxxxxx""" & _
"<br><br>" & _
"<br>Please click on the 5 links below and tell me what happens, thanks!" & _
"<br>1 <A HREF=""https://app.box.com/s/x01liz3ralbdrt152i52fpwb0wx40ff3"">link to box net cloud free file sharing</A>" & _
"<br>2 <A HREF=""file://" & ThisWorkbook.Path & "\" & "Test file DOThtm to be stored on your computer to try to open with a Hyperlink in a received EMail.htm"""">htm file on your computer</A>" & _
"<br>3 <A HREF=""file://" & ThisWorkbook.Path & "\" & "Test file DOThtm to be stored on your computer to try to open with a Hyperlink in a received EMail.htm"""">htm file on your computer</A>" & _
"<br>4 <A HREF=""file://" & ThisWorkbook.Path & "\" & "Empty test file DOTxls stored on your computer to try to open from Hyperlink in arrived Email.xls"""">empty xls file on your computer</A>" & _
"<br>5 <A HREF=""file:///" & ThisWorkbook.Path & "\" & "Empty test file DOTxls stored on your computer to try to open from Hyperlink in arrived Email.xls"""">empty xls file on your computer</A>"
.To "xxxxxxxxxxxxxxxxxxxx"
.CC "xxxxxxxxxxxxxxxxxxx"
.BCC = ""
.from = """Fahrradprinzessinunterwegs@gmail.com"" <Fahrradprinzessinunterwegs@gmail.com>"
.Subject = "Sent from EMail address: Fahrradprinzessinunterwegs@gmail.com"
.htmlbody = strHTML
.Send ' Do it
End With ' 6a(ii) CreateObject("CDO.Message") ---my Created LCDCW Library
End Sub
Sub SendfromDocAlnsteinGermanTelekom()
Rem 6 EMail send 'For info see: http://www.excelfox.com/forum/showthread.php/2233-Urgent-support-needed-Multiple-emails-multiple-Excel-workbooks-at-once#post10519
'Working at my end With my With End With Created LCDCW Library, (LCD 1.0 Library ) (Linking Configuration Data_Cods Wollups)
'6a(i)
With CreateObject("CDO.Message") ' -Linking Cods Wollups--------
Dim LCD_CW As String: Let LCD_CW = "http://schemas.microsoft.com/cdo/configuration/"
.Configuration(LCD_CW & "smtpusessl") = True '
.Configuration(LCD_CW & "smtpauthenticate") = 1 '
' ' Sever info
.Configuration(LCD_CW & "smtpserver") = "securesmtp.t-online.de" ' "smtp.gmail.com" ' "smtp.office365.com" ' "smtp.live.com" ' "smtp-mail.outlook.com" ' "smtp.live.com" ' "smtp.gmail.com" ' "securesmtp.t-online.de" "smtp.mail.yahoo.com" "smtp.live.com" "pod51017.outlook.com" "smtp-mail.outlook.com" "smtp.live.com" "securesmtp.t-online.de"
' The mechanism to use to send messages.
.Configuration(LCD_CW & "sendusing") = 2 ' Based on the LCD_OLE Data Base of type DBTYPE_I4
.Configuration(LCD_CW & "smtpserverport") = 465 ' 465 or 25 for gmail '587 ' 25 ' 465 or 25 for t-online.de 'or 587 'or 25
'
.Configuration(LCD_CW & "sendusername") = "Doc.Alnstein@t-online.de" '
.Configuration(LCD_CW & "sendpassword") = "xxxxxxxxx" ' '
' .Configuration(LCD_CW & "sendusername") = "YourEMailAddress"
' .Configuration(LCD_CW & "sendpassword") = "YourEMailPassword"
' Optional - How long to try
.Configuration(LCD_CW & "smtpconnectiontimeout") = 30 '
' Intraction protocol is Set/ Updated
.Configuration.Fields.Update '
'End With 6a(i)' ---------------------- my Created LCDCW Library
'6a(ii) With ' -- ' Data to be sent--- my Created LCDCW Library
Dim strHTML As String: Let strHTML = "<font size=""3"" face=""Calibri"">" & _
"This is sent from EMail account:" & _
"<br>Username: ""Doc.Alnstein@t-online.de""" & _
"<br>Password: ""xxxxxxxxxxx""" & _
"<br><br>" & _
"<br>Please click on the 5 links below and tell me what happens, thanks!" & _
"<br>1 <A HREF=""https://app.box.com/s/x01liz3ralbdrt152i52fpwb0wx40ff3"">link to box net cloud free file sharing</A>" & _
"<br>2 <A HREF=""file://" & ThisWorkbook.Path & "\" & "Test file DOThtm to be stored on your computer to try to open with a Hyperlink in a received EMail.htm"""">htm file on your computer</A>" & _
"<br>3 <A HREF=""file://" & ThisWorkbook.Path & "\" & "Test file DOThtm to be stored on your computer to try to open with a Hyperlink in a received EMail.htm"""">htm file on your computer</A>" & _
"<br>4 <A HREF=""file://" & ThisWorkbook.Path & "\" & "Empty test file DOTxls stored on your computer to try to open from Hyperlink in arrived Email.xls"""">empty xls file on your computer</A>" & _
"<br>5 <A HREF=""file:///" & ThisWorkbook.Path & "\" & "Empty test file DOTxls stored on your computer to try to open from Hyperlink in arrived Email.xls"""">empty xls file on your computer</A>"
.To "xxxxxxxxxxxxxxxxxxxxxxxx"
.CC "xxxxxxxxxxxxxxxxxxxxxxxxx"
.BCC = ""
.from = """Doc.Alnstein@t-online.de"" <Doc.Alnstein@t-online.de>"
.Subject = "Sent from EMail address: Doc.Alnstein@t-online.de"
.htmlbody = strHTML
.Send ' Do it
End With ' 6a(ii) CreateObject("CDO.Message") ---my Created LCDCW Library
End Sub
Instructions:
Three files are attached. Please download them and store them all somewhere on your computer. They can be stored anywhere, but important is that they are all stored in the same Folder :
All 3 files stored in same place.JPG : https://imgur.com/rFu0TML
Please open only one file “Test File xxxx to send EMail containing Hyperlinks to Files.xlsm”
Enable macros.
There are three codes in file “Test File xxxx to send EMail containing Hyperlinks to Files.xlsm”.
The codes are very similar, differing only in the Email account used as the .Sender:
Sub SendfromDocAlnsteinGermanTelekom()
Sub SendfromFahrradprinzessinunterwegsgmail()
Sub SendfromExcelVBAExpgmail()
Please try to run those codes.
Each code should send you an Email which on arrival will look something similar to this:
Typical received EMail.JPG : https://imgur.com/4oNXNtW
Please click on the 5 Hyperlinks and tell me what happens.
My final goal is to get a Hyperlink which when clicked opens an Excel or Word File.
I have tested the codes sending to my gmail and German Telekom Email accounts.
But so far, only link 1 works. But link 1 does not open a file: It simply sends you to a file sharing site. So link 1 is a temporary solution for me.
Code for Thai in next post....