从Outlook电子邮件获取某些文本字符串 [英] get certain text string from outlook email
问题描述
我在电子邮件中尝试在公司名称:之后的电子邮件正文中收到文字。并将其放入Excel表格中。
I am trying to get the text from an email body after the word "Company Name:" in my email. and put it into an excel spread sheet.
所以例如我们有公司名称:John's Building Services
So for instance if we had Company Name: John's Building Services
然后我们将在公司名称后面得到文字:所以我们的结果将是John's Building Services
Then we would get the text after Company Name: so our result would be 'John's Building Services'
我使用的是以下代码:
Dim b4 As String
b4 = olkMsg.Body
Dim indexOfNameb As Integer
indexOfNameb = InStr(1, b4, "Company Name: ")
Dim finalString4b As String
finalStringb = Right(b4, Len(b4) - indexOfNameb - 13)
excWks4.Cells(intRow4, 1) = finalStringb
这样可以获得公司名称后的文字,但是在我的电子邮件正文中,我也有如下的行文字: / p>
This gets the text after Company name, however in my email body I also have text on the lines below like:
Company Name: John's Building Services
Company number: 123
Company Status: live
etc
etc
我得到的问题是我的代码返回公司名称后面的所有内容:包括所有行文以下,我只想直接在公司名称后面取得文本:这应该是约翰的建筑服务。
the problem im getting is my code returns everything after company name: including all the text on the lines below and I only want to get the text directly after company name: which should be John's Building Services.
有人可以告诉我我哪里错了。谢谢
Can someone please show me where I am going wrong. Thanks
推荐答案
您的代码正确,您从公司名称的起始索引到最终的字符串为$ code> Right()正在工作 - 它从字符串的末尾开始使用特定数量的字符。
你想要的是找到公司号码的开始索引(或者你有那里的任何终止字符 - 可能是vbCrLf),并使用 Mid(br,开始索引,长度)
其中长度为Len(b4) - 公司编号的开始索引。
Your code is correct, you're getting string from start index of Company name to the end as Right()
is working - it takes specific number of chars starting from end of string.
What you want is to find start index of Company number (or any termination char you have there - vbCrLf maybe?) and use Mid(br, start index, length)
where length is Len(b4)-start index of company number.
您应该添加以下代码:
Dim indexOfNamec As Integer
indexOfNamec = InStr(1, b4, "Company number: ")
而不是
finalStringb = Right(b4, Len(b4) - indexOfNameb - 13)
使用
finalStringb = Mid(b4, indexOfNameb, indexOfNamec - indexOfnameb)
如果定义了finalString的适当长度,那么这个 indexOfNamec - indexOfnameb c不能确定。
I'm not sure about this indexOfNamec - indexOfnameb
calculation if it defines proper length of finalString, but it should help.
全部测试子代码:
Sub test()
Dim b4 As String
b4 = "blablablabla Company Name: CompName Company number: CompNumber blablablablabla"
Dim indexOfNameb As Integer
indexOfNameb = InStr(1, b4, "Company Name: ")
Dim indexOfNamec As Integer
indexOfNamec = InStr(1, b4, "Company number: ")
Dim finalStringb As String
finalStringb = Mid(b4, indexOfNameb, indexOfNamec - indexOfNameb)
Sheets(1).Cells(1, 1) = finalStringb
End Sub
这篇关于从Outlook电子邮件获取某些文本字符串的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!