vba - 在 VBA 中将单元格的值写入另一个 Excel
问题描述
我有这个代码可以通过 Outlook 发送带有附件的电子邮件:
Sub AutoEmail()
On Error GoTo Cancel
Dim Resp As Integer
Resp = MsgBox(prompt:=vbCr & "Yes = Review Email" & vbCr & "No = Immediately Send" & vbCr & "Cancel = Cancel" & vbCr, _
Title:="Review email before sending?", _
Buttons:=3 + 32)
Select Case Resp
'Yes was clicked, user wants to review email first
Case Is = 6
Dim myOutlook As Object
Dim myMailItem As Object
Set otlApp = CreateObject("Outlook.Application")
Set otlNewMail = otlApp.CreateItem(olMailItem)
FName = Application.ActiveWorkbook.FullName
With otlNewMail
.To = Cells(33, 10)
.CC = Cells(1, 1)
.Subject = Cells(23, 10) & ": " & Cells(21, 10)
.Body = "Good morning" & vbCr & vbCr & "" & Cells(23, 10) & "."
.Attachments.Add FName
.Display
End With
Set otlNewMail = Nothing
Set otlApp = Nothing
Set otlAttach = Nothing
Set otlMess = Nothing
Set otlNSpace = Nothing
'If no is clicked
Case Is = 7
Dim myOutlok As Object
Dim myMailItm As Object
Set otlApp = CreateObject("Outlook.Application")
Set otlNewMail = otlApp.CreateItem(olMailItem)
FName = ActiveWorkbook.Path & "\" & ActiveWorkbook.Name
With otlNewMail
.To = ""
.CC = ""
.Subject = ""
.Body = "Good Morning," & vbCr & vbCr & " " & Format(Date, "MM/DD") & "."
.Attachments.Add FName
.Send
'.Display
'Application.Wait (Now + TimeValue("0:00:01"))
'Application.SendKeys "%s"
End With
'otlApp.Quit
Set otlNewMail = Nothing
Set otlApp = Nothing
Set otlAttach = Nothing
Set otlMess = Nothing
Set otlNSpace = Nothing
'If Cancel is clicked
Case Is = 2
Cancel:
MsgBox prompt:="No Email has been sent.", _
Title:="EMAIL CANCELLED", _
Buttons:=64
End Select
End Sub
但是,我还想补充一件事。我想在通过 Outlook 发送电子邮件后将一些单元格写入另一个 excel,比如说 A2 到 B15。我要写入的 excel 文件位于 C:\Users\Computername\Desktop\Savingdata.xlsx
解决方案
默特,
尝试以下操作,在代码开头添加这两行:
Dim wbThisWorkbook, wbTheOneToSaveTo As Workbook
Set wbThisWorkbook = Workbooks("TheNameOfYourCurrentWorkbook")
然后在您的发送例程之后,添加以下内容:
Set wbTheOneToSaveTo = Workbooks.Open ("C:\Users\Computername\Desktop\Savingdata.xlsx")
wbThisWorkbook.Sheets("TheNameOfThe Worksheet").Range("A2").Copy
wbTheOneToSaveTo.Sheets("TheNameOfTheWorksheet").Range("B15").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False 'adjust parameters according to your needs
wbTheOneToSaveTo.Close True
wbThisWorkbook.Activate
希望这可以帮助!
推荐阅读
- linux - 如何配置 Apache 服务器/crontab/bash 文件以允许在一天中的特定时间和/或从特定 IP 进行访问?
- python - python数据框中的一行中的复合命令?
- java - Saxon 9,Java,来自 doc-available 的 NullPointer
- ionic-framework - 如何在 Ionic 5 应用程序的大屏幕上使用 ion-infinite-scroll?
- java - spring-oauth2 中的不同授权端点
- python - Python中的连续重复
- oracle - Oracle 服务云 Rnow- 通过 api 拉取自定义报告
- python - python - 使用正常法则生成随机流量
- tensorflow - 无法将 .h5 文件转换为 tflite 模型
- c++ - 以某种方式用素数填充向量